Macro to Collapse columns on all worksheets between two tabs and then save as pdf

[MarkB5432]

Hi,

I have an excel workbook with around 50 tabs sandwiched in between a tab labelled 'start' and a tab labelled 'end'. The 50 tabs combined produce a single 50+ page report when saved as a pdf.

Within each of the 50 tabs there are rows/columns that I want to make sure are collapsed (grouped) prior to saving.

I basically need a single Excel macro that performs two actions:


Step 1
Group/Collapse relevant columns and rows between the 'start' and 'end' tabs

I've found the following VBA code below that works but only applies to a single active worksheet … not sure how to apply this to the workbook or specifically between the 'start' and 'end' tabs

Sub Collapse_All()
ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End Sub


Step 2
Select all the sheets between the 'Start' and 'End' tab and save as a single pdf file (this will be saved in the same folder location as the excel file)


I'm no good at writing macro's whatsoever, so I'm somebody could provide me with the code for a single macro that I can simply copy and paste it would be very much appreciated!!

Thank you in advance!!

 

[JLGWhiz]

This should apply the code to all sheets except "start" and "end"

Sub Collapse_All()
For Each sh In ThisWorkbook.Sheets   'assumes code will run from same workbook.
    If sh.name <> "start" And sh.Name <> "end" Then
       ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    End If
Next
End Sub

 

[MarkB5432]

Thank for your reply @JLGWhiz , Unfortunately that VBA only appears to be working for the active sheet, and not the sheets between the 'start' and 'end' tabs

 

[JLGWhiz]

Thank for your reply @JLGWhiz , Unfortunately that VBA only appears to be working for the active sheet, and not the sheets between the 'start' and 'end' tabs

My goof, change the line with ActiveSheet to:

sh.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1

 

[MarkB5432]

Thanks! - Just tested the VBA and it collapsed all the grouped columns in the workbook.

Is there any way of restricted the VBA to only group those sheets that fall between the 'start' and 'end' tabs, and not to whole workbook? (e.g. any tabs placed before and including the 'start' tab are not captured by the VBA)?

 

[John_w]

Try this for both steps.

Public Sub Save_Sheets_As_PDF()

    Dim currentSheet As Worksheet
    Dim replaceFlag As Boolean
    Dim i As Long
    
    With ThisWorkbook
        replaceFlag = True
        Set currentSheet = .ActiveSheet
        For i = .Worksheets("Start").Index + 1 To .Worksheets("End").Index - 1
            .Worksheets(i).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
            .Worksheets(i).Select replaceFlag
            replaceFlag = False
        Next
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Replace(.FullName, ".xlsm", ".pdf"), _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        currentSheet.Select
    End With
    
End Sub

 

[MarkB5432]

Awesome - Thanks @John_w - That worked a treat!!!