Forum Discussion
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 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
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")
9 Replies
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?
- AnthonyDavisSFCopper ContributorThat 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.