Run Hide Rows Macro on Specific Sheet before running different macro to pdf that sheet and four others

[mjb342]

Hello,

I have two macros. One simply PDFs five sheets based on what's listed in a table. The other hides rows based on a string in a specific column. I'd like to make sure the hide rows is run before I run the PDF so it looks cleaner. Is this possible. I'd tried simply "Call HideUnhideRows" . I use the same HideUnhideRows module on different tabs that I don't include in the PDF. It's okay if it runs them on each tab as well but some won't be part of the pdf. Not sure if this makes sense. Otherwise I'm going into one tab, clicking the hide rows button and the clicking PDF macro on another tab. Just like to execute all hide rows on all tabs then PDF?



PDF Code:

Public Sub CreateExcisePDF()
Dim TOCTable1 As ListObject
Dim PDFSheets() As String
Dim c As Byte 'number of tabs to be exported
Dim FileName As String
On Error GoTo Handle:

FileName = ThisWorkbook.Path & "\Excise Provision"
Set TOCTable1 = Worksheets("TOC").ListObjects("TOCTable1")
ReDim PDFSheets(1 To TOCTable1.DataBodyRange.Rows.Count)
'fill up the array
For c = 1 To UBound(PDFSheets)
PDFSheets(c) = TOCTable1.DataBodyRange(c, 1).Value
Next c
Worksheets(PDFSheets).Select
ActiveSheet.ExportAsFixedFormat xlTypePDF, FileName
Worksheets("TOC").Select
MsgBox "PDF file was created." & vbNewLine & "File is called Excise Provision. It is saved on the same directory as this workbook.", , "Well Done"
Exit Sub

Handle:
If Err.Number = 9 Then
    MsgBox "It looks like a tab name was not spelled correctly. Please double check."
Else
    MsgBox "Looks like error here. Please ensure sheets are visible..."
End If

End Sub

HideRows Code:

Sub HideRows()
Dim i As Long, LR As Long
LR = Range("BI" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("BI" & i).Value = "Hide" Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub

 

[GWteB]

Apparently you're populating the PDFSheets array with sheet names. If so, then you might consider to add a line of code in the next fragment like:

For c = 1 To UBound(PDFSheets)
    PDFSheets(c) = TOCTable1.DataBodyRange(c, 1).Value
    Call HideRows(Worksheets(PDFSheets(c))              ' <<<<<<<<
Next c

.... and to amend your separate sub like:

Public Sub HideRows(ByRef argSht As Worksheet)
    Dim i As Long, LR As Long
    With argSht
        LR = .Range("BI" & Rows.Count).End(xlUp).row
        For i = 1 To LR
            If .Range("BI" & i).Value = "Hide" Then
                .Rows(i).Hidden = True
            Else
                .Rows(i).Hidden = False
            End If
        Next i
    End With
End Sub