Forum Discussion
Solved
Workbook reference returns subscript out of range error
Bit embarrassing, but here I am... Currently struggling up the cliff that is self-learning VBA via yt and google while simultaneously trying to create a usable macro and I feel like I'm missing s...
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")
That part is me copying and trying to modify someone else's homework, and we all know how well that works out...
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.
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.
I understand now, thank you again! You've been a huge help.