VBA Save as PDF - Autoslect Sheet

[Ada01]

Hello

I have several workbooks that are used to track sales scores and payouts. Each book relates to a "group" and each group a region. The Workbooks all vary in the number of reps being totaled and calculated.

I have a "Control" worksheet that has various components of the saving process including the list of sheet names that need to be saved (they all begin in cell A13)

Is it possible to write a looping code that will export the individual worksheets by first selecting the worksheet from the cells column A and then running a code similar to the one below. "SelectedSheet" would be the variable I am not sure on (the sheet name from Column A). I had this working as a test using ActiveSheet.Name, but i want to be able to be more flexible an have this work across all workbooks.

The confirmation message can be removed.

Sub Other_ActiveSheetPDF()



    pdfName = SelectedSheet & "-" & Range("Rpt_Per")
    pdfLoc = Range("Save_Loc")
    WBname = Replace(ActiveWorkbook.Name, ".xlsm", "")
    fileSaveName = pdfLoc & SelectedSheet & " - " & WBname & " - " & Range("Rpt_Per")
    
     ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=fileSaveName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


    MsgBox "File Saved " & " " & fileSaveName




End Sub

Thanks

 

[Kenneth Hobson]

Were you wanting to put all the Set Print Areas on the sheets to one PDF file?

All you need here is to create an array of the sheet names. I will look at that if needed.

Sub PDFPrintAll()
  PublishAllToPDF Worksheets(Array("Record 1", "Record 2"))
  Worksheets("Record 2").Select
End Sub

Sub PDFPrint1()
  PublishAllToPDF Sheets(Array("Record 1"))
  Worksheets("Record 1").Select
End Sub

Sub PDFPrint2()
  PublishAllToPDF Sheets(Array("Record 2"))
  Worksheets("Record 2").Select
End Sub

Sub PublishAllToPDF(o As Object)
  Dim fn As String
  fn = ThisWorkbook.Path & "\" & GetBaseName(ThisWorkbook.Name) & ".pdf"
  o.Select
  PublishToPDF fn, ActiveSheet
  Range("A1").Select
End Sub

Sub PublishToPDF(fName As String, o As Object)
  Dim rc As Variant

  rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
  If rc = "" Or rc = False Then Exit Sub
  
  o.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rc _
  , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
  :=False, OpenAfterPublish:=False
End Sub

Function GetBaseName(Filespec As String)
  Dim FSO As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  GetBaseName = FSO.GetBaseName(Filespec)
End Function

 

[Ada01]

I need each sheet or, since i did not think about it print area, to be it's own pdf.

Thanks

 

[Ada01]

I was able to make some progress on this idea. Below i have the range defined that i want to look at, and then the start of the loop through the range. What i need to have clarified is how to select each worksheet within the loop and then export them with the naming convention defined within the xl export. I am not sure how to seclect the cell, open the worksheet and then return to the loop and not lose my spot.

Sub PDF_Export()

'Set the Looping Range to be Cells in Col A that represent the Sheets


    Dim LastRow As Long
    Dim LoopRange As String
    
    FirstRow = 13
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Let LoopRange = "A" & FirstRow & ":" & "A" & LastRow
    
'Looping Through the Defined Range above to export the sheet




    For Each cell In LoopRange


'************************************************************************
'********* NEED TO SECLECT WORKBOOK BASED ON ACTIVE CELL IN LOOP ********
'************************************************************************
      
      Application.Run ("ActiveSheetPDF")
      
    Next cell


    




End Sub

Sub ActiveSheetPDF()



    pdfName = ActiveSheet.Name & "-" & Range("Rpt_Per")
    pdfLoc = Range("Save_Loc")
    WBname = Replace(ActiveWorkbook.Name, ".xlsm", "")
    fileSaveName = pdfLoc & ActiveSheet.Name & " - " & WBname & " - " & Range("Rpt_Per")
    
     ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=fileSaveName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False


    MsgBox "File Saved " & " " & fileSaveName


    Sheets("Control").Select
    




End Sub

 

[Chuck6475]

Were you wanting to put all the Set Print Areas on the sheets to one PDF file?

All you need here is to create an array of the sheet names. I will look at that if needed.

Sub PDFPrintAll()
  PublishAllToPDF Worksheets(Array("Record 1", "Record 2"))
  Worksheets("Record 2").Select
End Sub

Sub PDFPrint1()
  PublishAllToPDF Sheets(Array("Record 1"))
  Worksheets("Record 1").Select
End Sub

Sub PDFPrint2()
  PublishAllToPDF Sheets(Array("Record 2"))
  Worksheets("Record 2").Select
End Sub

Sub PublishAllToPDF(o As Object)
  Dim fn As String
  fn = ThisWorkbook.Path & "\" & GetBaseName(ThisWorkbook.Name) & ".pdf"
  o.Select
  PublishToPDF fn, ActiveSheet
  Range("A1").Select
End Sub

Sub PublishToPDF(fName As String, o As Object)
  Dim rc As Variant

  rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
  If rc = "" Or rc = False Then Exit Sub
  
  o.ExportAsFixedFormat Type:=xlTypePDF, Filename:=rc _
  , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
  :=False, OpenAfterPublish:=False
End Sub

Function GetBaseName(Filespec As String)
  Dim FSO As Object
  Set FSO = CreateObject("Scripting.FileSystemObject")
  GetBaseName = FSO.GetBaseName(Filespec)
End Function

[/QUOTEI realize it has been awhile since you posted this code but I was hoping you could help me understand it.

I've used it and had gotten it to work but not because I understand what I was doing. While since I've learn significantly more VB, I still have difficulty understanding what is going on in PDFPrint1, 2 and AlltoPDF.

Now I would like to use it to print multiple calculated worksheet into one pdf, where the worksheet itself could be one of several different varieties.

 

[Kenneth Hobson]

It is best to start your own thread. You can always reference some other thread by thread ID number or hyperlink if it relates. Rather than quoting all of a post you can say something like in post #2 ...

PDFPrint1 prints a sheet to PDF. PDFPrint2 is the same as 1PDFPrint1 except it prints a different sheet. PDFPrintAll prints all sheets named in the array to one PDF. Keep in mind that no matter what order you use in the array, the order is defined by sheet index order.

To print multiple sheets out of sheet index order takes more code which I have show in other threads in various Excel forums.