CODEWITHSUNDEEP
excelvba
engrken
engrken

Reputation: 59

activesheet in excel VBA

Appologies for wery simple question i have, i'm just new in programming. Is the Activesheet in excel VBA means that if you have a code 

Sheets("Point Assignments").Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & lastrow).Select
Application.CutCopyMode = False
Selection.Copy

The ActiveSheet will be the "Point Assignments"

Also, in the lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row what is meant by .END(xlup). Row

Thanks very much for the help

Regards, Kenneth

Upvotes: 0

Views: 3561

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Yes, but using ActiveSheet and not explicitly referring to a specific sheet does not make for good code.

It's difficult in a larger project to track which sheet is supposed to be active at any given time, particularly if you're calling out to other procedures which also interact with the workbook, and might leave the workbook in a different state from where it began.

So - a better approach would be something like:

With Sheets("Point Assignments")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("B2:B" & lastrow).Copy
End With

or simpler:

With Sheets("Point Assignments")
    .Range(.Range("B2", .Cells(.Rows.Count, 1).End(xlUp)).Copy
End With

Upvotes: 2

Related Questions