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")
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.
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.