Forum Discussion
Workbook reference returns subscript out of range error
Replace the two lines
Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Set Trackingwbk = Workbooks("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
with
Set Trackingwbk = Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Can you explain the following part?
Rows(iRowlocation).EntireRow.Insert inserts a row on Trackingwsh (the active sheet) whose row number is iRowlocation, i.e. the value of cell A1 on Trackingwsh.
So for example if A1 on Trackingwsh contains the number 37, and if iCount = 6, you'll insert a new row in row 37 six times. Is that your intention?
Here's where I found it.
https://excelchamps.com/vba/insert-row/
The intention is to insert the number of active rows from the labelwsh into the trackingwsh in preparation to copy the data (haven't tried writing that part yet.)
I tried adapting it to be automatic by replacing the prompts with values but I see swapping the answer to the cells value is incorrect. If I understand this better now, I should be using the location of A1, or 1, instead of the value inside the cell?
I haven't wrapped my head around how it's supposed to work yet though so I guarantee there's ate least one issue. I was planning on messing with that after I got the reference issue fixed. Originally I wasn't going to include the entire code, but I know sometimes errors come from other places than you expect so included it in case it's part of the issue.
- Thank you! The reference issue makes sense now, I think.
So, let's see if I understand. If I kept OPEN as a separate line, then SET would only use the file name instead of the full path?
Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Set Trackingwbk = Workbooks("Sample Tracker.xlsx")
Would that work? Not that I'll use it, just trying to understand more.Yes, that should work too.
Replace the two lines
Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Set Trackingwbk = Workbooks("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
with
Set Trackingwbk = Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Woohoo! That solved that problem. Could've sworn that's how I originally tried it but must not have. Thank you!! Could you explain why there was an issue there? Hopefully it'll help me build some more foundational knowledge.
Now on to the mismatch on the irowlocation mismatch error. Again, thanks!!Once a workbook has been opened, Excel refers to it by just the file name, not by the full path + filename. That is why
Set Trackingwbk = Workbooks("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
failed. By assigning the variable Trackingwbk in the same line in which you open the workbook, you avoid that problem.