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

 

[Fluff]

Hi & welcome to Mr Excell
This code should print only hidden sheets, where the tab colour is blank & will hide all rows where column C is blank

Sub PrintOut()
' jiuk - infomation(s)
'xlSheetVisible = -1
'xlSheetHidden = 0
'xlSheetVeryHidden = 2
    Dim mySheet As Excel.Worksheet

Application.ScreenUpdating = True

    For Each mySheet In ThisWorkbook.Worksheets
        If mySheet.Visible <> -1 And mySheet.Tab.ColorIndex = xlColorIndexNone Then
            With mySheet
                .Columns(3).SpecialCells(xlBlanks).EntireRow.Hidden = True
                .PrintOut
            End With
        End If
    Next
    
End Sub

HTH

 

[sabbuck]

Thanks so much for this, a lot shorter than what I was trying to do. But it's not every row I want to check if the cell is blank. Say in Sheet 5, I'm using 84 rows. But it's only rows 6 to 13 I'd like to hide if the cell is empty. Is there a way of doing this?

Thanks again.

 

[Fluff]

Will it always be rows 6 to 13 for every sheet, or can it vary?
If it varies can we do rows 6 to last used row?

 

[sabbuck]

It can vary...to make it more complicated, on sheet 6 it will be the following rows: 6-11, 13-18, 21-26, 29-31, 34 and 36. I don't want to hide anything in between such as row 12 which is blank anyway as it's an empty row for space reasons. From row 41 to 83 I will never want to hide regardless of if it's blank.

I've really made it complicated for myself....but with the aim of making it easy for users.

 

[Fluff]

For each sheet will it always be the same rows?
If so I will need the actual sheet names & which rows to hide.
If the rows will vary every time, then there is a problem

 

[sabbuck]

The rows will vary each time. There's no way to make the sheets the same as different criteria is needed in each sheet so they will not be formatted the same way.

 

[Fluff]

Maybe I wasn't very clear.
you said

on sheet 6 it will be the following rows: 6-11, 13-18, 21-26, 29-31, 34 and 36.

For sheet6 will it always be these rows?

 

[sabbuck]

Oh sorry I misunderstood, yes it will always be these rows.

 

[Fluff]

ok

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 "[COLOR=#0000ff]Sheet 5[/COLOR]"
                        .Range("C6:C13").SpecialCells.EntireRow.Hidden = True
                        .PrintOut
                        [COLOR=#ff0000].Range("C6:C13").EntireRow.Hidden = False[/COLOR]
                    Case "[COLOR=#0000ff]Sheet 6[/COLOR]"
                        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"))
                        Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
                        .PrintOut
                        [COLOR=#ff0000]Rng.EntireRow.Hidden = False[/COLOR]
                End Select
            End With
        End If
    Next
    
End Sub

This should work on sheet 6, for sheet 5 you said it was just rows 6-13, if that is correct then it should work for that as well.
For the other sheets add a new case for each.
The bits in blue need to be the actual names of your sheets.
Whereas the bits in red will unhide the rows after printing, if you don't want that then just delete the lines.
Any queries just shout.