Running a Marcro when sheet is opened

I recorde a simple macro to hide all the blank cells in a sheet and pasted
into the code of a command button as follws:

Private Sub CommandButton1_Click()
Range("B6:K425").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

What I would like to do is have this macro apply when the sheet is opened
but I don't know what to enter after the 'Private Sub' bit instead of
CommandButton1_Click().

Can anyone help please.

Thanks a plenty in advance.

Try This :

Private Sub Worksheet_Activate()

Jason Zischke

"Mark Dullingham" wrote:

> I recorde a simple macro to hide all the blank cells in a sheet and pasted
> into the code of a command button as follws:
>
> Private Sub CommandButton1_Click()
> Range("B6:K425").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=1, Criteria1:="<>"
> End Sub
>
> What I would like to do is have this macro apply when the sheet is opened
> but I don't know what to enter after the 'Private Sub' bit instead of
> CommandButton1_Click().
>
> Can anyone help please.
>
> Thanks a plenty in advance.
>

Thanks for the quick response Jason.
Tried your suggestion but it didn't work!
Do I have to save or shut the workbook first?

"Jason Zischke" wrote:

> Try This :
>
> Private Sub Worksheet_Activate()
>
> Jason Zischke
>
> "Mark Dullingham" wrote:
>
> > I recorde a simple macro to hide all the blank cells in a sheet and pasted
> > into the code of a command button as follws:
> >
> > Private Sub CommandButton1_Click()
> > Range("B6:K425").Select
> > Selection.AutoFilter
> > Selection.AutoFilter Field:=1, Criteria1:="<>"
> > End Sub
> >
> > What I would like to do is have this macro apply when the sheet is opened
> > but I don't know what to enter after the 'Private Sub' bit instead of
> > CommandButton1_Click().
> >
> > Can anyone help please.
> >
> > Thanks a plenty in advance.
> >

Mark Dullingham,

Yes you can the sub should run you open your workbook and that sheet is
active or when you switch between another sheet and back to the sheet that
has that sub in it.

Jason Zischke

"Mark Dullingham" wrote:

> Thanks for the quick response Jason.
> Tried your suggestion but it didn't work!
> Do I have to save or shut the workbook first?
>
> "Jason Zischke" wrote:
>
> > Try This :
> >
> > Private Sub Worksheet_Activate()
> >
> > Jason Zischke
> >
> > "Mark Dullingham" wrote:
> >
> > > I recorde a simple macro to hide all the blank cells in a sheet and pasted
> > > into the code of a command button as follws:
> > >
> > > Private Sub CommandButton1_Click()
> > > Range("B6:K425").Select
> > > Selection.AutoFilter
> > > Selection.AutoFilter Field:=1, Criteria1:="<>"
> > > End Sub
> > >
> > > What I would like to do is have this macro apply when the sheet is opened
> > > but I don't know what to enter after the 'Private Sub' bit instead of
> > > CommandButton1_Click().
> > >
> > > Can anyone help please.
> > >
> > > Thanks a plenty in advance.
> > >

Removed some code that was attached to another command button that reversed
the filter and all is chipper now.
thanks for taking the time

"Jason Zischke" wrote:

> Mark Dullingham,
>
> Yes you can the sub should run you open your workbook and that sheet is
> active or when you switch between another sheet and back to the sheet that
> has that sub in it.
>
> Jason Zischke
>
> "Mark Dullingham" wrote:
>
> > Thanks for the quick response Jason.
> > Tried your suggestion but it didn't work!
> > Do I have to save or shut the workbook first?
> >
> > "Jason Zischke" wrote:
> >
> > > Try This :
> > >
> > > Private Sub Worksheet_Activate()
> > >
> > > Jason Zischke
> > >
> > > "Mark Dullingham" wrote:
> > >
> > > > I recorde a simple macro to hide all the blank cells in a sheet and pasted
> > > > into the code of a command button as follws:
> > > >
> > > > Private Sub CommandButton1_Click()
> > > > Range("B6:K425").Select
> > > > Selection.AutoFilter
> > > > Selection.AutoFilter Field:=1, Criteria1:="<>"
> > > > End Sub
> > > >
> > > > What I would like to do is have this macro apply when the sheet is opened
> > > > but I don't know what to enter after the 'Private Sub' bit instead of
> > > > CommandButton1_Click().
> > > >
> > > > Can anyone help please.
> > > >
> > > > Thanks a plenty in advance.
> > > >