vba find last row in column B and resize table range

userIxHl1 0
Viewed 24

I have two macro enabled workbooks. One is used to collect data by multiple users (Macro for Tracker) and the other one (Tracker) is just place to store data. "Macro for Tracker" will open the "Tracker", copy paste values in specific columns, save and close it. Now, I am trying to resize table range in the Tracker itself to the last row in column B, but keep failing here.

Tracker contains columns with formulas and has only one code for timestamp. I was trying to add a private sub, which will run upon opening, but it does not work and gives me error "subscript out of range":

Dim ws As Worksheet
Dim ob As ListObject
Dim Lrow1 As Long

Lrow1 = Sheets("Main").Cells(Rows.Count, "B").End(xlUp).Row
Set ws = ActiveWorkbook.Worksheets("B")
Set ob = ws.ListObjects("Table2")

ob.Resize ob.Range.Resize(Lrow1)

Also I was trying to add lines in "Macro for Tracker" workbook, to resize after it will copy paste, but it gets more complicated. I am lost here.table range

vba excel
1 Answers

In your code, you appear to be referencing a worksheet called B. I can only assume (without seeing your workbook) that you actually meant Main..

Try this

Dim ws As Worksheet
Dim ob As ListObject
Dim Lrow1 As Long

Set ws = ActiveWorkbook.Worksheets("main")
Lrow1 = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

Set ob = ws.ListObjects("Table2")
ob.Resize ob.Range.Resize(Lrow1)
CLR0
Related