manekankit
Board Regular
- Joined
- Feb 1, 2019
- Messages
- 72
- Office Version
- 365
- Platform
- Windows
There is a worksheet called "Index" in column F. Various sheets are listed therein.
I am currently using below code to export listed sheets as pdf. Currently all the sheets are individually exported.
I want all of them to be saved as pdf in a signle pdf file with file name as mentioned in cell F1 of the "Index" Sheet.
Requesting help in modifying below vba code.
I am currently using below code to export listed sheets as pdf. Currently all the sheets are individually exported.
I want all of them to be saved as pdf in a signle pdf file with file name as mentioned in cell F1 of the "Index" Sheet.
Requesting help in modifying below vba code.
VBA Code:
Private Sub PDF_Stand()
Sheets("Index").Activate
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ruta As String, ruta2 As String, ruta3 As String, ruta4 As String, ruta5 As String, nFile As String, wName As String
Dim ws As Worksheet, sh As Worksheet, hoja As String
Dim u As Long, exist As Boolean
Set wb = ActiveWorkbook
nFile = Format(Date, "dd-mmm-yyyy")
dt = Format(CStr(Now), "hh_mm")
ruta = wb.Path & "\"
ruta2 = ruta & "export"
If Dir(ruta2, vbDirectory) = Empty Then
MkDir ruta2
End If
ruta3 = ruta2 & "\" & "Pdf"
If Dir(ruta3, vbDirectory) = Empty Then
MkDir ruta3
End If
ruta4 = ruta3 & "\" & nFile
If Dir(ruta4, vbDirectory) = Empty Then
MkDir ruta4
End If
ruta5 = ruta4 & "\" & dt
If Dir(ruta5, vbDirectory) = Empty Then
MkDir ruta5
End If
'
Set ws = Sheets("Index")
u = ws.Range("F" & Rows.Count).End(xlUp).Row
For i = 2 To u
hoja = ws.Cells(i, "F").Value
exist = False
wName = hoja & " " & "[" & Format(Date, "dd-mmm-yy") & "]"
For Each sh In Sheets
If LCase(sh.Name) = LCase(hoja) Then
exist = True
Exit For
End If
Next
If exist Then
Sheets(hoja).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ruta5 & "\" & "[St] " & wName & " [" & dt & "]" & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next
Application.ScreenUpdating = True
Dim Resp As VbMsgBoxResult
Resp = MsgBox("PDFs Created." & vbNewLine & "You should look for the file at " & ruta5 & vbNewLine & vbNewLine & "Do you want to open the folder?", vbInformation + vbYesNo, "Report ready")
If Resp = vbYes Then
ThisWorkbook.FollowHyperlink ruta5
End If
End Sub