Run a maco when worksheet opens

[jwgreen]

Hi All,

Im sure this one is an easy one but I cant seem to figure it out. Ive seached but found nothing that helps. What I need is to run a marco when I open the worksheet that it is in. Heres what I have.

Sub Cell_Select()
ActiveWorkbook.Sheets("MAR").Activate
    Range("B7").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
End Sub

Any help is appreciated. Thanks in advance!

 

[Diablo II]

this must be add to the Thisworkbook model. in the VBA editor right click thisworkbook and click View code and past it in.

Sub workbook_open()
ActiveWorkbook.Sheets("MAR").Activate
    Range("B7").Select
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
End Sub

 

[Smitty]

If all you're trying to do is get to the first empty cell in column B, then why not just use:

Sheets("MAR").Cells(Rows.Count."B").End(xlup).Offset(1)

I'll be a whole lot faster than selecting and looping like that.

In addition, if you're trying to do this on all sheets (as it seems likely you have 12 monthly sheets), I'd replace "Sheets("MAR")" with "ActiveSheet" and put the code in the WorkBook_SheetActivate event.

HTH,

 

[jwgreen]

Smitty,

I messed around with the code but could not get it to work. This is what I have placed in the ThisWorkbook Module.

Sub WorkBook_SheetActivate()
ActiveSheet.Cells(Rows.Count."B").End(xlup).Offset(1)
End Sub

I get the error "Compile error: Expected: identifier or bracketed expression"

Thanks for the help.

 

[Smitty]

ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1).Select

You have a period before "B".

 

[jwgreen]

Smitty,

Ahh I see it now.... but Now Im getting a "Procedure declaration does not match description of event or procedure having same name" Error.

 

[Legacy 68668]

Now Im getting a "Procedure declaration does not match description of event or procedure having same name" Error.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

 

[jwgreen]

Seiya,

Thanks for the help but once again another error now

"Object doesn't support this property or method (Error 438)"

Heres the code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Offset (1)
End Sub

 

[Legacy 68668]

Is this what you are trying to do ?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Cells(Rows.Count, "B").End(xlUp).Offset(1).Activate
End Sub