VBA Export each ws with condition to separate PDF

[AJS1984]

Trying to loop through each sheet and export to PDF if Cell A1 = 1, using ws name in PDF name

I got this far and was able to export all conditional to one PDF but can't get it to separate files, Currently script doesn't do anything and im stumped.

Public Sub Export_Sheets_To_PDF()

    Dim saveInFolder As String
    Dim replaceSelected As Boolean
    Dim wsName As Variant
    Dim ws As Worksheet

    saveInFolder = "C:\Users\Documents\01-ReportFiles\"
    'Save to onedrive
    'saveInFolder = ThisWorkbook.Path
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    With ThisWorkbook
        replaceSelected = True
         For Each ws In Worksheets
            If Range("A1").Value = 1 Then
                .Worksheets(ws).ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Master_00").Range("K2").Text & "_" & ws & "_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
                    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            End If
        Next   
    End With
End Sub

Any pointers where i've messed this up?

Thanks
AJ

 

[John_w]

Replace the loop with:

         For Each ws In .Worksheets
            If ws.Range("A1").Value = 1 Then
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Master_00").Range("K2").Text & "_" & ws.Name & "_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
                    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            End If
        Next

 

[AJS1984]

Replace the loop with:

         For Each ws In .Worksheets
            If ws.Range("A1").Value = 1 Then
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Master_00").Range("K2").Text & "_" & ws.Name & "_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
                    Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            End If
        Next

Thanks for taking a look @John_w

When i run it it throws a

I had a similar error and removed the ws. on the IF which cleared the Type mismatch but then threw

This is where my current vba knowledge lacks.

Any ideas would be greatly appreciated ?

 

[John_w]

When i run it it throws a

What is the value of A1 on the sheet which fails? Does it contain an error such as #N/A, #DIV/0!, #NUM!, etc.?

Check for the error like this:

    With ThisWorkbook
         For Each ws In .Worksheets
            If Not IsError(ws.Range("A1").Value) Then
                If ws.Range("A1").Value = 1 Then
                    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Master_00").Range("K2").Text & "_" & ws.Name & "_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
                        Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
                End If
            End If
        Next
    End With

 

[AJS1984]

What is the value of A1 on the sheet which fails? Does it contain an error such as #N/A, #DIV/0!, #NUM!, etc.?

Check for the error like this:

    With ThisWorkbook
         For Each ws In .Worksheets
            If Not IsError(ws.Range("A1").Value) Then
                If ws.Range("A1").Value = 1 Then
                    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Master_00").Range("K2").Text & "_" & ws.Name & "_" & Format(Date, "YYYY-MM-DD") & ".pdf", _
                        Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
                End If
            End If
        Next
    End With

Thanks John_w, that did the trick. Didn't think I had any errors but I had some that were letters and therefore couldn't parse the comparison.