VBA: Open workbook to last empty row

[bamaisgreat]

I have a workbook named "Archive" and a sheet named "Master" which I would like for It to go to the last empty row upon opening. Is there away to do this...Thanks

 

[jon49]

Open the VBE editor Alt+F11

Open ThisWorkbook module.
Click on drop down menu to "Workbook"
It should create the procedure:

Private Sub Workbook_Open()


End Sub

Write this code in the procedure:
dim rng as range
dim wks as worksheet
set wks=thisworkbook.worksheet("Master")
wks.select
set rng=wks.range("A" & application.rows.count)
rng.end(xlup).offset(1).select

This hasn't been verified but is should work!

 

[bamaisgreat]

It bugged on this line
wks=thisworkbook.worksheet("Master")

 

[jon49]

Did you remember to put in the "set" keyword?

It should be:
set wks=thisworkbook.worksheet("Master")

 

[bamaisgreat]

yes, the error Compile error: Method of Data member not found
then it highlights the bold area

Private Sub Workbook_Open()
Dim rng As Range
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheet("Sheet1")
wks.Select
Set rng = wks.Range("A" & Application.Rows.Count)
rng.End(xlUp).Offset(1).Select

End Sub

 

[ColdGeorge]

Hi all

Give this a try:

Private Sub Workbook_Open()
Sheets(1).Select
Range("A65536").End(xlUp).Offset(1, 0).Select
End Sub


You may change sheets number and column as necessary

ColdGeorge

 

[JLGWhiz]

It bugged on this line
wks=thisworkbook.worksheet("Master")

Should be Worksheets("Master")

 

[jon49]

Sorry, I should have tested it first for you, or at least written it in the VBE (sometimes I like to think I know it all ).

"Worksheet" should be "Worksheets"

 

[bamaisgreat]

Lol Thanks that did it..