Select Sheets & Export To PDF Based On Cell Value

uklobster

New Member
Joined
Dec 3, 2003
Messages
30
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 :)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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
 
Upvote 0
Based on your OP (no need for the formula):
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top