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

3
votes

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
3
vbaexcel
Don't understand. You want it hidden in which workbook? – findwindow
You need at least one sheet visible. In your code, near the end, you set all sheets to hidden. Try setting "Cover" to True. – Wayne G. Dunn
My Parent file has (calculation file +9 other sheet). Those 9 sheets are hidden but 9 hidden files take data from calculation file. Once my calculation file is completed I click generate report. It creates NEW FILE which has 9 sheets and doesn't have calculation file anymore. These 9 sheets come from Parent file (I want them all visible in New file but remain hidden in Parent file). My macro unhides 9 files in parent sheet (which i dont want). and hides file in newly generated file (again which i dont want). So it does reverse of both i want. – Eric
You switch between saying 'sheets' and 'files'... which is is please? After your code runs to Unhide, then hide, you never save the original workbook again -- you do a SaveAs -- which creates a new Workbook. Easy part first: in your above code, get rid of all the the ....Visible = False lines of code. That will result in your new workbook sheets being visible. – Wayne G. Dunn

3 Answers

1
votes

Would suggest you tweak this to ensure that Cover exists first.

If so the loop to hide the sheets can be set more directly - doesn't actually need a Then test.

Sub Sheeted()
Dim ws As Worksheet

On Error Resume Next
Set ws = Sheets("Cover")
On Error GoTo 0

If ws Is Nothing Then Exit Sub

For Each ws In ActiveWorkbook.Sheets
    ws.Visible = (ws.Name = "Cover")
Next
End Sub
0
votes

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
0
votes

Thank you guys I some how by trial and error and with your help figured it out. I just put .close thing which i saw somewhere on this group.

Here is the modified code based on all the suggestions. May have some unnecessary things but somehow it works. Many Thanks to you all 

Sub SaveMain()

Dim Flname As String
Dim ws As Worksheet

Application.EnableEvents = False
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next

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

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

newfilename = Flname

With ActiveWorkbook
.SaveAs newfilename, FileFormat:=50
.Close 0
End With
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "Calculations" Then
 ws.Visible = xlSheetVeryHidden
 End If

Next

Application.EnableEvents = True

End Sub