Macro to save different worksheets as PDFs to a folder and name them based on their sheet

[jondavis1987]

I'm trying to create a macro that saves different worksheets in my workbook as different PDFs. The worksheets are called "T-88" "T-89 & T-90" "T-99" and "Report".

All the PDFs need to go into this location. C:\Users\jdavis\Dropbox\Quality Control\Jobs. From the jobs folder it needs to find the name of the job and go into that folder. The name of the job will be in cell C6 of the report. From there it needs to go into a folder called Soils. So it should save in a folder path of basically C:\Users\jdavis\Dropbox\Quality Control\Jobs\Cell C6\Soils.

The name of the PDF needs to be the name in Cell J5 of Report worksheet and the name of the sheet basically. So if the name in J5 is 1200+00 there should be four PDFs named 1200+00 T-88, 1200+00 T-89 & 90, 1200+00 T-99, 1200+00 Report.

 

[jondavis1987]

Here is what I have so far. But I don't know how to switch the active sheet to the sheet names.

Sub save_Worksheets()


    Dim WB As Workbook
    
    
    Dim fName As String
    




'   Export T-88to PDF
    
    With srcWB
  Dim LocationName As String
     fName = Range("'Report'!J5").Value
     LocationName = Range("'Report'!C6").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & LocationName & "\Soils\" & fName & " T-88", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True




'   Export T-89 & T-90 to PDF
     
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\\" & LocationName & "\Soils\" & fName & " T-89 & T-90", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True




'   Export T-99 to PDF


     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\\" & LocationName & "\Soils\" & fName & " T-99", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


'   Export Report to PDF


     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\\" & LocationName & "\Soils\" & fName & " Report", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True




End With
End Sub

 

[jondavis1987]

I ended up getting it. Used the following if anybody is interested.

Sub save_Worksheets()


    Dim WB As Workbook
    Dim fName As String
    




'   Export T-88to PDF
    
    With srcWB
  Dim LocationName As String
     fName = Range("'Report'!J5").Value
     LocationName = Range("'Report'!C6").Value
     Worksheets("T-88").Activate
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & LocationName & "\Soils\" & fName & " T-88", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


End With
'   Export T-89 & T-90 to PDF
     
         With srcWB
     fName = Range("'Report'!J5").Value
     LocationName = Range("'Report'!C6").Value
     Worksheets("T-89 & T-90").Activate
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & LocationName & "\Soils\" & fName & " T-89 & T-90", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


End With
'   Export T-99 to PDF


    With srcWB
     fName = Range("'Report'!J5").Value
     LocationName = Range("'Report'!C6").Value
     Worksheets("T-99").Activate
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & LocationName & "\Soils\" & fName & " T-99", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
'   Export Report to PDF
    With srcWB
     fName = Range("'Report'!J5").Value
     LocationName = Range("'Report'!C6").Value
     Worksheets("Report").Activate
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & LocationName & "\Soils\" & fName & " Report", Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True








End With




End Sub

 

[John_w]

Another way, without activating each sheet:

Public Sub Save_Sheets_As_PDF()

    Dim saveInFolder As String
    Dim ws As Worksheet
    
    saveInFolder = "C:\Users\jdavis\Dropbox\Quality Control\Jobs\" & Worksheets("Report").Range("C6").Value & "\Soils\"
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    For Each ws In Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & Worksheets("Report").Range("J5").Value & " " & ws.Name & ".pdf", _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
    
End Sub

 

[Fluff]

Duplicated https://www.mrexcel.com/forum/excel-questions/1049406-vba-error-code-suspected-end.html#post5038638

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).