VBA Formula

[North_Yorks]

Hi, I have a spreadsheet that uses VBA to hide rows upon opening. I would like the workbook to open on the first tab. I have seen the formula to use for this:

Private Sub Workbook_Open()

Worksheets("Example").Activate

End Sub

However as my workbook page already uses a formula to collapse / hide rows in other sheet upon opening I'm not sure where to fit this in to my set of instructions.

These are the instruction I have and I have put the new one at the top but it doesn't work. Please help!

Private Sub Workbook_Open()

Worksheets("General Instructions").Activate

Sheets("Americas").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:49").EntireRow.Hidden = True

Sheets("ANZASAF").Activate
Rows("8:18").EntireRow.Hidden = True
Rows("21:24").EntireRow.Hidden = True
Rows("27:36").EntireRow.Hidden = True
Rows("39:45").EntireRow.Hidden = True

Sheets("CEU").Activate
Rows("9:18").EntireRow.Hidden = True
Rows("21:30").EntireRow.Hidden = True
Rows("33:42").EntireRow.Hidden = True
Rows("45:50").EntireRow.Hidden = True

Sheets("GB").Activate
Rows("8:16").EntireRow.Hidden = True
Rows("19:27").EntireRow.Hidden = True
Rows("30:39").EntireRow.Hidden = True
Rows("42:47").EntireRow.Hidden = True

End Sub

[#2]

@North_Yorks 

Private Sub Workbook_Open()

Worksheets("General Instructions").Activate

With Sheets("Americas")
.Rows("8:18").EntireRow.Hidden = True
.Rows("21:30").EntireRow.Hidden = True
.Rows("33:42").EntireRow.Hidden = True
.Rows("45:49").EntireRow.Hidden = True
End With

With Sheets("ANZASAF")
.Rows("8:18").EntireRow.Hidden = True
.Rows("21:24").EntireRow.Hidden = True
.Rows("27:36").EntireRow.Hidden = True
.Rows("39:45").EntireRow.Hidden = True
End With

With Sheets("CEU")
.Rows("9:18").EntireRow.Hidden = True
.Rows("21:30").EntireRow.Hidden = True
.Rows("33:42").EntireRow.Hidden = True
.Rows("45:50").EntireRow.Hidden = True
End With

With Sheets("GB")
.Rows("8:16").EntireRow.Hidden = True
.Rows("19:27").EntireRow.Hidden = True
.Rows("30:39").EntireRow.Hidden = True
.Rows("42:47").EntireRow.Hidden = True
End With

End Sub

[#3]

@Craig Hatmaker 

Thank you for your response i have added the End With that I was missing and I get this error message when I open the file now

[#4]

@North_Yorks 
I'm not sure I understand what you mean by " added the End With that I was missing". Perhaps you are missing that instead of 

Sheets("Americas").Activate

I'm using 

With Sheets("Americas")

and where you have something like 

Rows("8:18").EntireRow.Hidden = True

I have added a period to the front so it is now:

.Rows("8:18").EntireRow.Hidden = True

I'm using an entirely different approach that happens to look very similar. You are trying to activate the sheet and hoping that the rows will reference the active sheet. I'm explicitly telling Excel to link the rows to the specific worksheet and I'm not activating each sheet to do it. By not activating each worksheet, your first activate remains the active worksheet.

Without the WITH statement the new code would look like this (which may make things clearer),

Sheets("Americas").Rows("8:18").EntireRow.Hidden = True
Sheets("Americas").Rows("21:30").EntireRow.Hidden = True
Sheets("Americas").Rows("33:42").EntireRow.Hidden = True
Sheets("Americas").Rows("45:49").EntireRow.Hidden = True

Sheets("ANZASAF").Rows("8:18").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("21:24").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("27:36").EntireRow.Hidden = True
Sheets("ANZASAF").Rows("39:45").EntireRow.Hidden = True

The WITH statement allows us to not duplicate the Sheets("sheet name") on every row.

[#5]

@Craig Hatmaker apologies I see my formula was missing more than I thought.

I have now copied your original instructions and it has worked. Many thanks

[#6]

@North_Yorks 

If you want to make the same changes on multiple sheets, a more succinct version might be

Option Explicit

Private Sub Workbook_Open()
    HideRows
End Sub

Sub HideRows()
Dim ws As Worksheet
    For Each ws In Sheets
        If ws.Name <> "GeneralInstructions" Then
            ws.Rows("8:18").Hidden = True
            ws.Rows("21:30").Hidden = True
        End If
    Next
    Sheets("GeneralInstructions").Select
End Sub

It is the final (or in this case the only) Select that determines what you see when control is returned from the macro. 

[I didn't bother about EntireRow because the Row object is already the entire row]

Sub UnhideRows()
Dim ws As Worksheet
    For Each ws In Sheets
        ws.Rows.Hidden = False
    Next
End Sub

Hi @Craig Hatmaker  Good to see you around!  Do not hesitate to improve my programming style,  It has a long way to go!