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.
Dim ws As Worksheet
Dim sSheet As Worksheet
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)
ReDim Preserve SheetsFound(UBound(SheetsFound) - 1)
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ActiveWorkbook.Path & "NEEDS TO BE C2 AND J2 and save as pdf.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _