Active worksheet as variable broken

[spencer_time]

Hello guys, I need help again. I have a snippit that works when the sheet is defined (sheet3) but doesn't work when I try to make a variable for the active sheet (ws). I get an error that says "object doesn't support this property or method" and when I press debug it highlights my variable ws.

The following is the WORKING code with the sheet hardcoded in:

Sub count()

Dim lastRow As Long
lastRow = Sheet3.Cells(Rows.count, 1).End(xlUp).Row
MsgBox lastRow

Dim lastColumn As Long
lastColumn = Sheet3.Cells(1, Columns.count).End(xlToLeft).Column
MsgBox lastColumn

With Sheet3.UsedRange
    MsgBox lastRow & " rows and " & lastColumn & " columns"
    MsgBox "Sum of number of rows and number of columns = " & (lastRow + lastColumn)
End With

End Sub

The following is the NON-WORKING code with me trying to use a variable for the active sheet:

Sub count_tst()
    Dim wb As Workbook ' test dim
    Dim ws As Worksheet ' test dim
    Set wb = Application.ActiveWorkbook ' test
    Set ws = Application.ActiveWorksheet ' test
    Dim lastRow As Long
    Dim lastColumn As Long
    
lastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
MsgBox lastRow

lastColumn = ws.Cells(1, Columns.count).End(xlToLeft).Column
MsgBox lastColumn

With ws.UsedRange
    MsgBox (lastRow + lastColumn)
End With

End Sub

Any help or suggestions appreciated both in helping with my problem or best practices that I might be doing in an inefficient manner.

-Trent

 

[Joe4]

Try:

Set ws = ActiveSheet

 

[spencer_time]

That worked like a charm, thank you Joe4. I knew it had to be something simple.

Would that same method work for the workbook?

Set wb = ActiveWorkbook

or would I want something like this:

Set wb = ActiveBook

EDIT: I wrote that in haste after reading your reply. After a quick search I see that it is the first solution Set wb = ActiveWorkbook.

Thanks again,
Trent

 

[Joe4]

I think I usually use ActiveWorkbook for the workbook one. So I think you are fine there.