Manchesterisred
New Member
- Joined
- Aug 20, 2021
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
Hello Everyone, I need your help on my Excel macro.
I have an excel file with 20 worksheets. I need the worksheets Individually converted to PDF and then saved in their own folder within a master folder. This is the macro i have so far - its basically been put together from previous macros i have used.
At the moment when i run it, it creates a new folder which is perfect but then it saves the PDF's outside of the folder.
I need the PDF's to be saved in their individual folders ( names the same as the worksheet and also a cell range name, inside of the main the master folder.
Can anyone suggest any ideas ??
Sub DD Letters()
'
' Direct debit Letters convert excel to PDF and save to drive
'
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationAutomatic
End With
'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook
'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd")
FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
MkDir FolderName
'Loop through all worksheets and save as individual PDF in same folder as the Excel file
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
FolderName = Path & "/" & ws.Name & ".pdf"
Next
'
End Sub
I have an excel file with 20 worksheets. I need the worksheets Individually converted to PDF and then saved in their own folder within a master folder. This is the macro i have so far - its basically been put together from previous macros i have used.
At the moment when i run it, it creates a new folder which is perfect but then it saves the PDF's outside of the folder.
I need the PDF's to be saved in their individual folders ( names the same as the worksheet and also a cell range name, inside of the main the master folder.
Can anyone suggest any ideas ??
Sub DD Letters()
'
' Direct debit Letters convert excel to PDF and save to drive
'
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationAutomatic
End With
'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook
'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd")
FolderName = Sourcewb.Path & "\" & "Direct Debit Letters dated - " & Format(Sourcewb.Worksheets("DD").Range("C32"), "dd.mm.yyyy")
MkDir FolderName
'Loop through all worksheets and save as individual PDF in same folder as the Excel file
For Each ws In ActiveWorkbook.Worksheets
ws.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
FolderName = Path & "/" & ws.Name & ".pdf"
Next
'
End Sub