save as code

I have the following code below that selects sheets if there is a value in C4 on each sheet in my workbook and saves them as a PDF. What I need to do is make it change the save as name to C2 & J2 on sheet "Sht 1 - Table 1" C2 being a name and J2 being a date.

Thanks
Rob.


Sub SaveSheetsAsPDF()
  Dim ws As Worksheet
  Dim sSheet As Worksheet
  Dim SheetsFound()
  ReDim SheetsFound(0)
    For Each sSheet In ActiveWorkbook.Worksheets
        If (sSheet.Range("C4").Value) >= 1 Then
            'Debug.Print sSheet.Name & " has data"
            SheetsFound(UBound(SheetsFound)) = sSheet.Name
            ReDim Preserve SheetsFound(UBound(SheetsFound) + 1)
        End If
    Next sSheet
    Debug.Print VarType(SheetsFound)
 ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
  Sheets(SheetsFound).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ActiveWorkbook.Path & "NEEDS TO BE C2 AND J2 and save as pdf.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub
RobJanineAsked:
Who is Participating?
 
dlmilleCommented:
My bad - the saveAsSheet should be sWks...

Here's the modified code:

 
Dim saveFileName As String
Dim sWks As Worksheet

  Set sWks = Sheets("Sht 1 - Table 1")
  
  saveFileName = ActiveWorkbook.Path & "\" & sWks.Range("C2").Value & Format(sWks.Range("J2").Value, "MM-DD-YYYY") & ".PDF"

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

Open in new window


Dave
0
 
dlmilleCommented:
Change the code to:

 
Dim saveFileName As String
Dim sWks As Worksheet

  Set saveAsSheet = Sheets("Sht 1 - Table 1")
  
  saveFileName = ActiveWorkbook.Path & "\" & sWks.Range("C2").Value & Format(sWks.Range("J2").Value, "MM-DD-YYYY") & ".PDF"

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

Open in new window


Dave
0
 
RobJanineAuthor Commented:
thanks

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.