Runtime Error 9 when using “With Sheet”

Question

I came up with a solution that should circumvent my original issue (found below VBA code so I can keep this short and sweet if extra info is unnecessary) but I get a runtime error9. I tested this code on two basic workbooks and it worked fine. The LastRow line is the line that highlights when debugging. I tried to surround the file name in single 's since the name has spaces but that didn't resolve the error.

Sub MakeGSATable()

Dim LastRow As Long
LastRow = Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR").Cells(Worksheets("GSR").Rows.Count, "A").End(xlUp).Row

With Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR")
.Range("A8").Select
.Range(Selection, Selection.End(xlToRight)).Select
.Range(Selection, Selection.End(xlDown)).Select
.ListObjects.Add(xlSrcRange, Range("$A$8:$AA$" & LastRow), , xlYes).Name = _
    "GSATable"
.ListObjects("GSATable").TableStyle = "TableStyleLight20"
End With

End Sub

As background: my main workbook has several macros and must be opened and kept in full screen and without the formula bar. I added a macro for workbook activate/deactivating so if users open another instance of Excel, it will work normally. One of the macros in my main book would pull up another workbook, fire the macros I need within it but at some point it gets caught up in the activate/deactivate macro, never actually finishing the macros that otherwise work without the activate/deactivate macro. So I decided to try and add this macro as I think it will be a good workaround.

There is probably no Worksheets("GSR") in ActiveWorkbook, so you probably need to use Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR") instead (when calculating .Rows.Count) — YowE3K
I guess this is handy to always write this long-winded string Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm") — JohnyL
Usin Selection and GSR worksheet. H-m-m-m... What could go wrong? — JohnyL
^^ All the Select statements will crash if "GSR" isn't the active worksheet but, as far as I can see, they are all redundant anyway and should therefore be deleted. But you should change Range("$A$8:$AA$" & LastRow) to be .Range("$A$8:$AA$" & LastRow). — YowE3K
Not everyone coming to ask a question here is an expert, @JohnyL. Your smart-aleck comments, esp re: "Selection" don't help anyone so thanks for nothing. I used the recorder to arrive at this and modified it as best I could. And my long-winded strings are to help me understand what's going on as I'm still pretty new to VBA with no actual class on it. When a file works as it should, I then go back and add variables for a smaller packaged code. — Craig

YowE3K YowE3K · Accepted Answer · 2017-12-13T19:21:00

At the moment your line

LastRow = Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR").Cells(Worksheets("GSR").Rows.Count, "A").End(xlUp).Row

is equivalent to

LastRow = Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR").Cells(ActiveWorkbook.Worksheets("GSR").Rows.Count, "A").End(xlUp).Row

so, if there is no "GSR" worksheet in the active workbook, it will give a "subscript out of range" error.

The line should actually say

LastRow = Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR").Cells(Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR").Rows.Count, "A").End(xlUp).Row

but that is getting a bit unwieldy, so it would be better if you moved it inside your With block:

With Workbooks("Reservation Activity Dashboard (RAD) CP.xlsm").Worksheets("GSR")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    '...

Runtime Error 9 when using “With Sheet”

2 Answers