Thanks - I was sort of getting there, but this is much neater
I will end up with at least 20 sheets where I want this function to work -
is there a way I can make each sheet Active without actually opening it?

Saintsman


"JRForm" wrote:

Saintsman-

Try this by placing this code below in the "Thisworkbook" code section.
when inserting a new sheet you will be asked if you want to hide rows 45-50.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call SaintsMan
End Sub


Sub SaintsMan()
Dim Msg, Style, Title, Response
Msg = "Do you want to hide rows ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Hide Some"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
ActiveSheet.Rows("45:50").Select
Selection.EntireRow.Hidden = True
Else
Exit Sub
End If

End Sub

"Saintsman" wrote:

Not just to set up worksheet
I will end up with perhaps 10 sheets where I want the option to Hide several
rows (always the same rows), but the remaining sheets should not be affected
My problem is that I can't create the sheets on day 1, new sheets will be
added over a period of time & I do not want to keep revisiting the workbook



"JRForm" wrote:

Saintsman-

So your worksheet has the condition of A1='Yes' then hide some rows. When
you say so will have sheet with the rows hidden and some won't, would you be
running this macro just to set up the worksheet?

"Saintsman" wrote:

Sorry Roger - not understanding what you mean here I'm afraid
The workbook will have several sheets where I do not want to hide any rows,
how do I differentate btween those that do & don't when I haven't created the
sheets yet

"Roger Govier" wrote:

Hi

If you make the reference to ActiveSheet rather than Sheets("Sheet1") in
your code, then it should work OK.

--
Regards
Roger Govier



"Saintsman" wrote in message
...
I need to hide several rows on sheet - ie if sheet1 A1 = YES hide rows
45-50
on sheet2 (I can mange that!), but...
Every time I make a copy of sheet2 I want the same functionality to apply
-always hiding/unhiding the same rows without having to amend any coding.
Is it possible?

Thanks in advance
Saintsman