sheets to PDF

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
74
Hi there,

I have a VBA code to split sheets, but now I want to save them as PDF.
And there for I have a code but do not know how to add to the code to the VBA code.
Can some help, I'm lost.....

Split code:
Private Sub CommandButton1_Click()
Dim Cl As Range
Dim ws As Worksheet
Dim Ky As Variant

Application.ScreenUpdating = False
Set ws = Sheets("OSAP Orders")
With CreateObject("scripting.dictionary")
For Each Cl In ws.Range("D2", ws.Range("D" & Rows.Count).End(xlUp).Offset(-1))
If Not .Exists(Cl.Value) And Cl.Value <> "" Then .Add Cl.Value, Nothing
Next Cl
For Each Ky In .Keys
ws.Range("A1").AutoFilter 4, Ky
Sheets.Add(, ws).Name = Ky
ws.AutoFilter.Range.EntireRow.Copy Sheets(Ky).Range("A1")
Sheets(Ky).Columns.AutoFit
Next Ky
End With
ws.AutoFilterMode = True
ws.Activate
End Sub

safe to PDF code:
'save splitted files to PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Documents" & Format(Now(), "yyyy-mm-dd") & " " & Cell.Value & ".pdf", _
OpenAfterPublish:=False


Greetings,
Steel010
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,570
.
This will save all sheets except OSAP Orders in the same folder as the workbook. You can edit the code
to further adhere to your requirements.

Code:
Option Explicit


Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim saveInFolder As String
    
    saveInFolder = ThisWorkbook.Path
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    


    For Each ws In Worksheets
       
            If Not (ws.Name Like "OSAP Orders") Then
                
                    ws.Select
                    nm = ws.Name
            
                    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:=saveInFolder & nm & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
            End If
        
    Next ws
        
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,450
Messages
5,523,065
Members
409,495
Latest member
bkahn

This Week's Hot Topics

Top