Q:

How do I Use VBA Macro on Hidden Worksheets. and Hide them back

I am a beginner in VBA excel Macros. By using this forum i could write a code but i am stuck at one place.
I have  10 sheets in a workbook. 1st Sheet is my input worksheet and does all the calculations. These calculations are pulled in later 9 sheets. These 9 sheets generate my report.In calculation file i want to hide all the next 9 files and run the macro. once the calculations are okay. I click a Generate report button and new file is created and opens on my desktop and get saved in my document.
Problem - Somehow going through this forum i was able to generate a code that does everything but i am not able to get Hide and unhide part.
The code i wrote unhide all the files from calculation file.(Keeps them unhide) and hides the file in newly generated report.(as all files cannot be hidden in any new excel i get run-time error'1004' unable to set the visible property of the worksheet class). 
here is my code please help me solve it - So i can have only 1 sheet in calculation file while rest remain hidden when macro works. And newly generated file will have all the 9 tabs.
Sub SaveMain()

Application.EnableEvents = False
Sheets("Cover").Visible = True
Sheets("2").Visible = True
Sheets("3").Visible = True
Sheets("4").Visible = True
Sheets("5").Visible = True
Sheets("6").Visible = True
Sheets("7").Visible = True
Sheets("8").Visible = True
Sheets("9").Visible = True

Dim Flname As String

Flname = "Pump Datasheet" & InputBox("Enter Pump tag No P-XXXX:") & ".xls"

Sheets(Array("Cover", "2", "3", "4", "5", "6", "7", "8", "9")).Copy

Sheets("Cover").Visible = False
Sheets("2").Visible = False
Sheets("3").Visible = False
Sheets("4").Visible = False
Sheets("5").Visible = False
Sheets("6").Visible = False
Sheets("7").Visible = False
Sheets("8").Visible = False
Sheets("9").Visible = False

newfilename = Flname

With ActiveWorkbook
.SaveAs newfilename, FileFormat:=50

End With

Application.EnableEvents = True

End Sub

A:

Try hiding and unhiding your sheets like this, it will save you a lot of energy if needing to change later.
Dim ws As Worksheet

'To unhide all sheets
For Each ws In ActiveWorkbook.Sheets
    ws.Visible = xlSheetVisible
Next

'To hide all sheets except "Cover"
For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "Cover" Then
        ws.Visible = xlSheetHidden
    End If
Next

