Button to run multiple macros at once

[sabbuck]

Hi All,

I have an excel workbook with 16 worksheets. Only one sheet is visible at a time for the user to fill out and then they click a button to go back to the main menu sheet. On the main menu sheet I would like a button to do the following:

1) print hidden worksheets - 10 of them, not all 16
2) on the sheets being printed hide specific rows based on a cell being empty when printing i.e. if cell C6 is empty then hide row 6 (it will not be the same row in all sheets)
3) number the pages in order when printing. Currently the sheets are in order of how I want them to print but the page numbers in the footer print page ? of ? for each sheet not for the number of sheets being printed altogether.

I tried the below but it printed all hidden sheets and didn't hide the rows with an empty cell (I only tried the empty cell macro for one sheet to test it). I changed the color of the tabs I didn't want printed to see if the below macro would work, it does on its own but not in conjunction with the other two.

Is what I'm asking impossible or is there a way of doing this?

The users filling out the sheets are far from excel experts and everything except the cells they need to fill out will be locked. So they won't be able to hide rows manually or print manually. Everything needs to be done by the click of a button.

Sub RunAllMacros()
Procedure1
Procedure2
Procedure3
End Sub


'print hidden sheets
Sub Procedure1()
' jiuk - infomation(s)
'xlSheetVisible = -1
'xlSheetHidden = 0
'xlSheetVeryHidden = 2
Dim mySheets As Excel.Worksheet
For Each mysheet In ThisWorkbook.Worksheets
If mysheet.Visible = 0 Or mysheet.Visible = 2 Then
If mysheet.Visible = 0 Then
With mysheet
.Visible = -1
.PrintOut
.Visible = 0
End With
End If
If mysheet.Visible = 2 Then
With mysheet
.Visible = -1
.PrintOut
.Visible = 2
End With
End If
End If
Next
End Sub


'print non coloured tabs
Sub Procedure2()
Application.ScreenUpdating = False
For Each Z In ThisWorkbook.Worksheets
If Z.Tab.ColorIndex = xlColorIndexNone Then
Z.PrintOut copies = 1
End If
Next


End Sub


'hide rows based on empty cell
Sub Procedure3()
Dim rw As Long
Dim rng As Range
Dim cell As Range


Application.ScreenUpdating = False


Set rng = Sheets("Sheet5").Range("C6:C14")


With rng.Columns(1)
For Each cell In rng
If Application.WorksheetFunction.CountA( _
.Parent.Cells(cell.Row, 1).Range("C6:C6")) = " " Then _
.Parent.Rows(cell.Row).Hidden = True
Next cell
.Parent.PrintOut
.EntireRow.Hidden = False
End With


Application.ScreenUpdating = True
End Sub

 

[sabbuck]

Now what happens when I click the button is it shows the print preview for sheet 5 "Worksheet - Membership" and Sheet 6 "Worksheet Products & Services" but the rows where the specified cell is blank are not hidden. No other sheets are shown in the print preview and the following error occurs: "Run-time error 1004: Unable to set the hidden property of the range class"

Any ideas?

Sub PrintOut()
'xlSheetVisible = -1
'xlSheetHidden = 0
'xlSheetVeryHidden = 2
Dim mySheet As Excel.Worksheet
Dim Rng As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Cl As Range


Application.ScreenUpdating = True


For Each mySheet In ThisWorkbook.Worksheets
If mySheet.Visible <> -1 And mySheet.Tab.ColorIndex = xlColorIndexNone Then
With mySheet
Select Case mySheet.Name
Case "Worksheet - Membership"
On Error Resume Next
.Range("C6:C13").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
.Visible = True
.PrintPreview
.Visible = False
.Range("C6:C13").EntireRow.Hidden = False
Case "Worksheet - Products & Services"
Set Rng1 = Union(.Range("J6:J11"), .Range("J13:J18"))
Set Rng2 = Union(.Range("J21:J26"), .Range("J29:J31"))
Set Rng = Union(Rng1, Rng2)
Set Rng = Union(Rng, .Range("J34:J36"))
On Error Resume Next
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
.Visible = True
.PrintPreview
.Visible = False
Rng.EntireRow.Hidden = False
Case "Risk Assessment Methodology"
Set Rng1 = Union(.Range("G8:G45"), .Range("I56:I60"))
Set Rng2 = Union(.Range("I62:I67"), .Range("I70:I75"))
Set Rng = Union(Rng1, Rng2)
Set Rng = Union(Rng, .Range("I78:I80"), .Range("I83:I85"))
On Error Resume Next
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
.Visible = True
.PrintPreview
.Visible = False
Rng.EntireRow.Hidden = False

End Select
End With
End If
Next

End Sub

 

[Fluff]

Which line of code do you get the error on?
Also when posting code please use the code tags, ie click the # icon & the paste your code in between them

 

[sabbuck]

On the second case the last line 'Rng.EntireRow.Hidden = False' is highlighted in yellow. I assume if it's highlighted in yellow that's where the error is coming from.

Thanks for the tip, I was wondering how everyone else's code posts so neatly.

 

[Fluff]

I assume if it's highlighted in yellow that's where the error is coming from.

That's quite right.
Unfortunately I'm not sure why you're getting that & I can't replicate it here.

 

[Fluff]

Just discovered a possibility, are your sheets protected?

 

[sabbuck]

Yes they are protected, only certain cells are allowed be edited by users. Will this cause a problem?

 

[Fluff]

Yes they are protected, only certain cells are allowed be edited by users. Will this cause a problem?

Yes it will, add the bits in red for each case

            Case "Summary"
                [COLOR=#ff0000].Unprotect ("pword")[/COLOR]
                On Error Resume Next
                .Range("C6:C13").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
                On Error GoTo 0
                .Visible = True
                .PrintPreview
                .Visible = False
                .Range("C6:C13").EntireRow.Hidden = False
                [COLOR=#ff0000].Protect Password:="pword"[/COLOR]
            Case "Names"

Changing pword to your password.
Would recomend recording a macro to protect the sheet, that way you can see which parameters you need to add

 

[sabbuck]

What I did in the end to print the hidden sheets I needed was the below code for each one. And then for the rows I needed hidden based on a cell value I just conditionally formatted the rows border, font and fill to white so it looks to have disappeared if the cell =0. Thank you for your help though, but for some reason the above codes were just not working for me. I'm sure if I had a better understanding of VBA code I'd figure it out. I'll get there one day.
#<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;"> Sub dw()
Dim wks As Worksheet


Set wks = Worksheets("someSheet")
With wks
.Visible = xlSheetVisible
.Printout
.Visible = xlSheetVeryHidden
End With
End Sub
</code>

 

[Fluff]

Glad you got it sorted