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”).
<tbody>
</tbody>
I got the below VBA from another website (extendoffice) which only exports 1 sheet (last “Y”) rather than all the “Y”.
Thanks in advance for your time.
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”).
A | B | C | D | E | F | G |
SL .No. | Work Sheet Name | Print Status | ||||
1 | Summery Sheet | |||||
2 | A One Polymer Ltd | Y | ||||
3 | Altech Aluninium Industries Ltd | Y | ||||
4 | Building Product & Service | |||||
5 | Arif Kabir Enterprise | Y |
<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: