Workbook reference returns subscript out of range error

[AnthonyDavisSF]

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 some REALLY basic info that I can't seem to find googling. I'm trying to create a macro to append data from a repeatedly used template into a larger tracking database file.

Where I keep running into issues is (I think?) setting the trackingwbk object to the specific filepath for said file. I added the open file script to make sure the destination file is open for referencing in case that was it. No change. I've moved around the dims and set=s as well as rewritten the thing differently a few times, all to the same result. The open file script works great so I'm pretty sure the filepath is fine and I'm missing something silly from VBA101...

The template being used won't have a file name yet so I'm using thisworkbook to reference it, but it does have constant sheet names that I've referenced specifically.

The macro isn't even close to finished, but this speed bump has me stalled. I'm pretty sure I'm misunderstanding something about the looping of the 'inserting new rows' section as well. 

All the answers or other situations I've seen have been for much more complicated or specific situations. I'm hoping some people here are still willing to help the beginners haha. Thanks in advance!

Sub Sample_tracker_copypaste()

Dim Trackingwbk As Workbook
Dim Trackingwsh As Worksheet

Dim Labelwbk As Workbook
Dim Labelwsh As Worksheet

Workbooks.Open ("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")

Set Trackingwbk = Workbooks("C:\Users\Anthony\SynologyDrive\Foldername\Sample Tracker.xlsx")
Set Trackingwsh = Worksheets("SampleTracker")

Set Labelwbk = ThisWorkbook
Set Labelwsh = ThisWorkbook.Worksheets("Records")

'count active rows'
Labelwsh.Range("L6") = Range("A2:A14").Cells.SpecialCells(xlCellTypeConstants).Count

'inserting new rows numbering the same as the L6 value'
Dim iRowlocation As Variant
Dim iCount As Variant
Dim i As Variant

iCount = Labelwsh.Range("L6")
iRowlocation = Trackingwsh.Range("A1")

For i = 1 To iCount
Rows(iRowlocation).EntireRow.Insert
Next i

End Sub

[#2]

@AnthonyDavisSF 

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?

[#3]

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.

[#4 · Accepted solution]

@AnthonyDavisSF 

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

[#5]

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!!

[#6]

@AnthonyDavisSF 

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.

[#7]

@AnthonyDavisSF 

If you want to insert lines in row 1, simply use

iRowlocation = 1

[#8]

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.

[#9]

@AnthonyDavisSF 

Yes, that should work too.

[#10]

I understand now, thank you again! You've been a huge help.