Export specific sheet(s) based on cell values into single Pdf

Belalhp

New Member
Joined
Oct 18, 2018
Messages
4
Hello... I'm having some difficulties... your help regarding the matter will be highly appreciated.

I have around 100 plus worksheets. 1st sheet is the Summery Sheet, in column B I have worksheet names and based on the value in column G I want to print that worksheet.
If column G has “Y” value (e.g. in G2, G3 & G5) then the relevant worksheets (e.g. worksheet names mentioned in B2, B3 & B5) will be exported as single PDF file (named “Print”).

ABCDEFG
SL .No. Work Sheet NamePrint Status
1Summery Sheet
2A One Polymer LtdY
3Altech Aluninium Industries LtdY
4Building Product & Service
5Arif Kabir EnterpriseY

<tbody>
</tbody>

I got the below VBA from another website (extendoffice) which only exports 1 sheet (last “Y”) rather than all the “Y”.

Code:
[COLOR=#000080]Sub CreateControlSheet()
'UpdatebyExtendoffice20170811
    Dim i As Integer
    Dim xCSheetRow As Integer
    Dim xSName As String
    Dim xCSheet As Variant
    Dim xRgVal As String
    On Error Resume Next
    xSName = "Summery Sheet"
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xCSheet = ActiveWorkbook.Worksheets(xSName)
    xCSheetRow = xCSheet.Range("G65536").End(xlUp).Row
    For i = 2 To xCSheetRow
        xRgVal = xCSheet.Range("G" & i).Value
        If xRgVal = "Y" Or xRgVal = "y" Then
            If xCSheet.Range("B" & i).Value <> "" Then
                ActiveWorkbook.Worksheets(xCSheet.Range("B" & i).Value).Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\Users\Accounts\Desktop\Print.pdf", Quality:= _
                xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
                Sheets("Summery Sheet").Select
            End If
        End If
    Next
    Sheets("Summery Sheet").Select
    Range("G2:G500").Select
    Selection.Replace What:="y", Replacement:="C", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("G1").Select
    Application.ScreenUpdating = True
End Sub[/COLOR]

Thanks in advance for your time.
 
Last edited by a moderator:

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,313
Office Version
  1. 365
Platform
  1. Windows
Remove this line
Code:
[COLOR=#000080]On Error Resume Next[/COLOR]
and see what happens. I suspect that you get an error, if so what does the error message say & what line is highlighted if you click debug
 

Belalhp

New Member
Joined
Oct 18, 2018
Messages
4
No error occurred, I got a PDF file named Print in Desktop as usual which has only 1 page (the last sheet where I entered “y”).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,313
Office Version
  1. 365
Platform
  1. Windows
Ok, you're not changing the name of the Pdf file, so that it's just overwriting itself everytime
 

Belalhp

New Member
Joined
Oct 18, 2018
Messages
4
Thx for the help. Is there anyway solution?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,313
Office Version
  1. 365
Platform
  1. Windows
Try making this change
Code:
            If xCSheet.Range("B" & i).Value <> "" Then
                ActiveWorkbook.Worksheets(xCSheet.Range("B" & i).Value).Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
                "C:\Users\Accounts\Desktop\Prin[COLOR=#0000ff]t " & i & ".[/COLOR]pdf", Quality:= _
                xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
                Sheets("Summery Sheet").Select
            End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
76,313
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Forum statistics

Threads
1,172,024
Messages
5,878,783
Members
433,370
Latest member
mcleven

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
Top