Amend VBA Code to Save 1x Page Only

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi there,

I have the below VBA code which successfully saves the worksheet to PDF. However, I would like to save only a particular page of the worksheet. Is this possible?
In the example below, I would like it to save page 1 of 21 pages. Which is also cells A1:J54

Thank you!

VBA Code:
Sub S4_Shareholder_1()
   
    Dim wsA As Worksheet
 Dim wbA As Workbook
 Dim strTime As String
 Dim strName As String
 Dim strPath As String
 Dim strFile As String
 Dim strPathFile As String
 Dim myFile As Variant
 Dim blnWasSheetHidden As Boolean
 On Error GoTo errHandler

 Set wbA = ActiveWorkbook
 Set wsA = Sheet76

 'get active workbook folder, if saved
 strPath = wbA.Path
 If strPath = "" Then
 strPath = Application.DefaultFilePath
 End If
 strPath = strPath & ""

 strName = wsA.Range("D14").Value _
 & " - " & wsA.Range("B6").Value _
 & " " & Format(wsA.Range("I11"), "dd-mmm-yy")

 'create default name for savng file
 strFile = strName & ".pdf"
 strPathFile = strPath & strFile

 'use can enter name and
 ' select folder for file
 myFile = Application.GetSaveAsFilename _
 (InitialFileName:=strName & ".pdf", _
 FileFilter:="PDF Files (*.pdf), *.pdf", _
 Title:="Select Folder and FileName to save")

 'export to PDF if a folder was selected
 If myFile <> "False" Then
 If wsA.Visible = xlSheetHidden Then
    wsA.Visible = xlSheetVisible
    blnWasSheetHidden = True
End If
 wsA.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=myFile, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=False
 If blnWasSheetHidden = True Then
    wsA.Visible = xlSheetHidden
 End If
 'confirmation message with file info
 MsgBox "PDF file has been created: " _
 & vbCrLf _
 & myFile
 End If

exitHandler:
 Exit Sub
errHandler:
 MsgBox "Could not create PDF file"
 Resume exitHandler
 End Sub
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Watch MrExcel Video

Forum statistics

Threads
1,113,890
Messages
5,544,879
Members
410,643
Latest member
sng
Top