VBA - Assign multiple worksheets as one variable

[kgallego]

Hello all,

Is there a way to assign multiple worksheets as one variable? Example:

Dim wsA as Worksheet
Dim wsB as Worksheet
Dim wsC as Worksheet

????Set wsABC = wsA & wsB & wsC????

Thanks,

Kelsey

 

[LockeGarmin]

Hello,

Does the code below behave how you expect it to? If I put your sheets in the wrong order in the "MySheetsIntoPdf" method please put them in the order you wish to have them exported in before running the script. Hope that helps!

Sub MySheetsIntoPdf()

  Call ExportSheetsAsPdf(ThisWorkbook.Path & "\TestPdf.pdf", Sheet11, Sheet12, Sheet4)

End Sub

Sub ExportSheetsAsPdf(FileName As String, ParamArray exportedSheets() As Variant)

Dim WB As Workbook
Dim Sheet As Object
Dim i As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set WB = Workbooks.Add
  Set Sheet = exportedSheets(LBound(exportedSheets))
  Sheet.Copy After:=WB.Sheets(1)
  WB.Sheets(1).Delete
  
  For i = LBound(exportedSheets) + 1 To UBound(exportedSheets)
    exportedSheets(i).Copy After:=WB.Sheets(WB.Sheets.Count)
  Next i
  
  WB.ExportAsFixedFormat XlFixedFormatType.xlTypePDF, FileName, _
    Quality:=xlQualityStandard, OpenAfterPublish:=True
  
  WB.Close False
  
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  
End Sub

 

[kgallego]

Hello,

Does the code below behave how you expect it to? If I put your sheets in the wrong order in the "MySheetsIntoPdf" method please put them in the order you wish to have them exported in before running the script. Hope that helps!

Sub MySheetsIntoPdf()

  Call ExportSheetsAsPdf(ThisWorkbook.Path & "\TestPdf.pdf", Sheet11, Sheet12, Sheet4)

End Sub

Sub ExportSheetsAsPdf(FileName As String, ParamArray exportedSheets() As Variant)

Dim WB As Workbook
Dim Sheet As Object
Dim i As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set WB = Workbooks.Add
  Set Sheet = exportedSheets(LBound(exportedSheets))
  Sheet.Copy After:=WB.Sheets(1)
  WB.Sheets(1).Delete
  
  For i = LBound(exportedSheets) + 1 To UBound(exportedSheets)
    exportedSheets(i).Copy After:=WB.Sheets(WB.Sheets.Count)
  Next i
  
  WB.ExportAsFixedFormat XlFixedFormatType.xlTypePDF, FileName, _
    Quality:=xlQualityStandard, OpenAfterPublish:=True
  
  WB.Close False
  
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  
End Sub

You are awesome!!! That is exactly what I've been trying to get done for a week now! Thanks so much for your help! -Kelsey-

 

[LockeGarmin]

Great! You're welcome! I'm glad it worked for you!

I realized after the fact that when a new workbook is created for most people excel starts with 3 tabs (though I personally always turn it to 1), so the previous version of ExportSheetsAsPdf may have more blank worksheets in the temporary workbook then what is deleted. It turns out this may not matter since ExportAsFixedFormat doesn't actually export blank worksheets so the code below will most likely have the same outcome, but I included a revision for completeness that should cover some corner cases. The code just looks neater too in my opinion.

Sub ExportSheetsAsPdf(FileName As String, ParamArray exportedSheets() As Variant)

Dim WB As Workbook
Dim Sheet As Variant

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set WB = Workbooks.Add
  Do Until WB.Sheets.Count = 1
    WB.Sheets(2).Delete
  Loop
  
  For Each Sheet In exportedSheets
    Sheet.Copy After:=WB.Sheets(WB.Sheets.Count)
  Next Sheet
  
  WB.Sheets(1).Delete
  
  WB.ExportAsFixedFormat XlFixedFormatType.xlTypePDF, FileName, _
    Quality:=xlQualityStandard, OpenAfterPublish:=True
  
  WB.Close SaveChanges:=False
  
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  
End Sub

 

[kgallego]

Well thank you again!

You wouldn't know how to tell it to create bookmarks after it create the PDF file would you?

Thanks,

Kelsey

 

[LockeGarmin]

I've never done that myself and I'm actually not sure what a PDF bookmark actually is or how to use them in my PDF viewer (which for me is google chrome). A little searching and I found this post that seems to suggest that bookmarks are created at each sheet of the document, but I'm not sure how to tell if it's true or not. Sorry I can't be much more help than that!

https://social.technet.microsoft.co...ks-from-excel-names?forum=officeitproprevious

 

[kgallego]

I've never done that myself and I'm actually not sure what a PDF bookmark actually is or how to use them in my PDF viewer (which for me is google chrome). A little searching and I found this post that seems to suggest that bookmarks are created at each sheet of the document, but I'm not sure how to tell if it's true or not. Sorry I can't be much more help than that!

https://social.technet.microsoft.co...ks-from-excel-names?forum=officeitproprevious

Thanks, I looked at that yesterday. Looks like this is going to be a tough one to crack. I posted a new thread yesterday about it and got zero responses. I'm sure there's a way to do it, but I'm not skilled enough with VBA to do it.

I'd think that they way to go about it would to add the bookmarks to the PDF after it's created and saved with a separate subroutine. Something like:

• Pull the pdf file in this .path with name "xxxxx".
• Add bookmark to page 1 with name "xxxx"
• Add subbookmark to page 2 with name "xxxx"

I'm not nowhere near that language savvy to know how to do that.

Thanks again to everyone for all their help! This has helped me quite a bit.

-Kelsey-