Ungroup Sheets from an array in VBA

Question

I've been trying to get an easy printout (in PDF using a single button) of one sheet with only active range and one chart located in another sheet. I've got everything working, except after I print, both sheets are grouped together and I can't edit my chart.

I'm trying to make this foolproof and easy for coworkers during real time operations. Right now I can right-click and select 'Ungroup sheets' to fix it, but I hate to have to do that each time (or explain that it needs to be done).

I tried to select a sheet, a different sheet, only one sheet etc. I can't figure out how to get VBA to ungroup the sheets at the end. Any ideas?

Sub CustomPrint()

'if statement to ask for file path
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
         & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

        If FixedFilePathName = "" Then
            'Open the GetSaveAsFilename dialog to enter a file name for the PDF file.
            FileFormatstr = "PDF Files (*.pdf), *.pdf"
            fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
                  Title:="Create PDF")

            'If you cancel this dialog, exit the function.
            If fname = False Then Exit Sub
        Else
            fname = FixedFilePathName
        End If

'Dynamic reference to RT drilling data
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Dim sht As Worksheet

Set sht = Worksheets("rt drilling data")
Set StartCell = Range("A1")

'Refresh UsedRange
  Worksheets("rt drilling data").UsedRange

'Find Last Row
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Select Range
 sht.Range("A1:K" & LastRow).Select

Sheets("Chart Update").Activate
ActiveSheet.ChartObjects(1).Select

ThisWorkbook.Sheets(Array("chart update", "RT drilling data")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=fname, IgnorePrintAreas:=False
'If the export is successful, return the file name.
        If Dir(fname) <> "" Then RDB_Create_PDF = fname
    End If
 If OverwriteIfFileExist = False Then
            If Dir(fname) <> "" Then Exit Sub
        End If

   On Error GoTo 0

Worksheets("ws model updates").Select

End Sub

They should ungroup when you select the last sheet. I Selected a group, the same as you do, and then when I select sheet3, the group dissolves. Sheets(Array("Sheet1", "Sheet2")).Select Sheets("Sheet3").Select — Hrothgar

Unknown Unknown · Accepted Answer · 2016-10-07T20:40:38

If Dir(fname) <> "" Then Exit Sub will bypass Worksheets("ws model updates").Select

If OverwriteIfFileExist = False Then
    If Dir(fname) <> "" Then 

        Worksheets("ws model updates").Select
        Exit Sub

    End If
End If

Ungroup Sheets from an array in VBA

1 Answers