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]

Thank you so much for your help. I'll try this now and let you know if it works.

 

[Fluff]

My previous code will crash if there are no rows to be hidden, use this instead

Sub PrintOut()
' jiuk - infomation(s)
'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 "Sheet 5"
                        On Error Resume Next
                        .Range("C6:C13").SpecialCells.EntireRow.Hidden = True
                        On Error GoTo 0
                        .PrintOut
                        .Range("C6:C13").EntireRow.Hidden = False
                    Case "Sheet 6"
                        Set Rng1 = Union(.Range("C6:C11"), .Range("C13:C18"))
                        Set Rng2 = Union(.Range("C21:C26"), .Range("C29:C31"))
                        Set Rng = Union(Rng1, Rng2)
                        Set Rng = Union(Rng, .Range("C34:C36"))
                        On Error Resume Next
                        Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
                        On Error GoTo 0
                        .PrintOut
                        Rng.EntireRow.Hidden = False
                End Select
            End With
        End If
    Next
    
End Sub

 

[sabbuck]

"Compile Error. Argument not Optional" is the error that comes up and it highlights the word "SpecialCells" in the code.

I wonder if I made a mistake somewhere adding in the next case. Ive also changed PrintOut to PrintPreview as I don't want to waste paper until I get it right.

This is the code I have now that brings up the error.

Sub PrintOut()
' jiuk - infomation(s)
'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"
.Range("C6:C13").SpecialCells.EntireRow.Hidden = True
.PrintPreview
.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"))
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintPreview
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"))
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintPreview
Rng.EntireRow.Hidden = False

End Select
End With
End If
Next

End Sub

 

[Fluff]

The error is because you have no blank cells in the range, add the 2 bits in red above & below every line using SpecialCells

                        [COLOR=#ff0000]On Error Resume Next[/COLOR]
                        .Range("C6:C13").SpecialCells.EntireRow.Hidden = True
                        [COLOR=#ff0000]On Error GoTo 0[/COLOR]

 

[sabbuck]

Error Still happening. Just checking if I've done that right.

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.EntireRow.Hidden = True
On Error GoTo 0
.PrintPreview
.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
.PrintPreview
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
.PrintPreview
Rng.EntireRow.Hidden = False

End Select
End With
End If
Next

End Sub

Thanks for your patience by the way, I'm quite good at excel but completely new to macros.
I wonder if it's because the button I've assigned this macro to is the only sheet visible when the button is clicked (which is the way I want it to be). Would all sheets need to be visible for this to work?

 

[Fluff]

My mistake this

.Range("C6:C13").SpecialCells.EntireRow.Hidden = True

Should be

.Range("C6:C13").SpecialCells[COLOR=#0000ff](xlCellTypeBlanks)[/COLOR].EntireRow.Hidden = True

 

[sabbuck]

Now it says "Run-time error '1004' No cells were found.

 

[Fluff]

Do you still have the error handlers above & below ie

[COLOR=#ff0000]On Error Resume Next[/COLOR]
.Range("C6:C13").SpecialCells[COLOR=#0000ff](xlCellTypeBlanks)[/COLOR].EntireRow.Hidden = True
[COLOR=#ff0000]On Error GoTo 0[/COLOR]

 

[sabbuck]

yes they are still there. I'll show you what I have so far:
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
.PrintPreview
.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
.PrintPreview
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
.PrintPreview
Rng.EntireRow.Hidden = False

End Select
End With
End If
Next

End Sub

 

[Fluff]

To use PrintPreview the sheet must be visible so

                .Visible = True
                .PrintPreview
                .Visible = False

But the error you mentioned in post#17 should be dealt with by the error handlers