Select Sheets & Export To PDF Based On Cell Value

[uklobster]

Hope someone can help, I don't know anything about VBA

I have a workbook with 3 worksheets which may or may not need exporting to pdf. On each sheet 'Windows', 'Doors' and 'Screens' if cell AG3>0 then I want to include that sheet in the export. This gives seven possible combinations.

Windows only
Doors only
Screens only
Windows and Doors
Windows and Screens
Doors and Screens
Windows, Doors and Screens

Is there a quick way to automate this so I can just press a button and only the necessary sheets are exported ?

Thanks for your help

 

[uklobster]

To explain further, this is the code I am using at the moment - and I must stress I know nothing about VBA this is just code I have cobbled together.

Sub exportpdf()
Dim FName As String
Dim FPath As String
Dim FNamea As String
Dim FPatha As String
Application.DisplayAlerts = False
FPath = "C:\Users\User\Google Drive\Projects\pdf"
FName = Sheets("Saves").Range("b1").Text
Sheets(Array("Windows", "Doors", "Screens")).Select
Sheets("Windows").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FPath & "" & FName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Sheets("MAIN").Select
Range("a1").Select
FPatha = "C:\Users\User\Google Drive\Projects\Excel"
FNamea = Sheets("Saves").Range("b2").Text
ThisWorkbook.SaveAs Filename:=FPatha & "" & FNamea, FileFormat:=52
Application.DisplayAlerts = True
End Sub

As you can see it selects all three tabs everytime, when I only want it to select the sheets which have data in. I don't really want to have 7 buttons for each permutation. If anyone can help I would be extremely grateful. Thanks :D

 

[uklobster]

If it makes it easier i have created a formula to return a value between 1-7 depending on which sheets need selecting. So on sheet 'MAIN' cell 'A1' will contain a number between 1 and 7. Depending on the number the following sheets will need to be selected:

1 'Windows' only
2 'Doors' only
3 'Screens' only
4 'Windows' and 'Doors' only
5 'Windows' and 'Screens' only
6 'Doors' and 'Screens' only
7 'Windows', 'Doors' and 'Screens'

Hope this helps and doesn't confuse more :D

 

[John_w]

Based on your OP (no need for the formula):

Public Sub Export_Sheets_To_PDF()

    Dim saveInFolder As String
    Dim replaceSelected As Boolean
    Dim wsName As Variant
    
    saveInFolder = "C:\Users\User\Google Drive\Projects\pdf\"
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    With ThisWorkbook

        replaceSelected = True
        For Each wsName In Array("Windows", "Doors", "Screens")
            If .Worksheets(wsName).Range("AG3").Value > 0 Then
                .Worksheets(wsName).Select replaceSelected
                replaceSelected = False
            End If
        Next
            
        .ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & .Worksheets("Saves").Range("B2").Text & ".pdf", _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
        .Worksheets("Saves").Select True
    
    End With
    
End Sub

 

[uklobster]

John_w you are a legend :D

Worked brilliantly, really appreciate your help !!