Using Pages or pagebreaks to save PDFs

kwread

New Member
Joined
May 12, 2016
Messages
9
I have an excel worksheet with ~20 different pages that i would like to save individually as pdfs to specific folders. This is a process that I have to repeat often with fresh data.

My 1st idea is a macro that uses the ExportAsFixedFormat function, where i can use variables to update the location/filename each week.

I would like to use the pages themselves as an object to export but the example i followed to create a 'page' object:

Dim objPage As Page

Set objPage = ActiveWorkbook.ActiveWindow _
.Panes(1).Pages.Item(1)

Is giving me a Run-time 438 error (Object doesnt support this property or method)
Any thoughts on this?


Alternatively i could use the the VPageBreak option to create ranges for each page but im having trouble turning vpagebreak locations into ranges.

I am a relative newbie when it comes to VBA so sample code would be very helpful. Below is the test spreadsheet with my (very simplified) data structure, where the pages split before Entity 1, entity 2, etc



Entity 1
1data
2data
3data
4data
5data
THINGS
Adata
Bdata
Entity 2
6data
7data
7data
8data
8data
8data
THINGS
Cdata
Cdata
Cdata
Cdata
Cdata

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Just as an update, i found a workaround by creating little 'Entity 1 End' tags that go below each set of data, just outside the print area. The below is working (note some of that code is for testing purposes). Its not the elegant solution based off print area i was hoping for but seems to be quite functional.




Sub ByLenderExport()
'
' Macro that prints each lenders section to pdf and saves as new excel
'This is the test version


' Create the Range and lender variables
Dim rng As Range


Dim CurLender(1 To 2) As String
CurLender(1) = "Entity 1"
CurLender(2) = "Entity 2"


'Iterate through each lender
For i = 1 To 2


'create the start cell and ending cell for each lenders range
startcell = "A" & Application.WorksheetFunction _
.Match(CurLender(i), Worksheets(1).Range("A:A"), 0)
endcell = "c" & Application.WorksheetFunction _
.Match(CurLender(i) & " End", Worksheets(1).Range("A:A"), 0) - 1

'set the range based on the cells
Set rng = Application.Range(Cell1:=startcell, Cell2:=endcell)

'export the range as a pdf, to the folder and name based on lender
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\kread\Desktop\PDF split test" & CurLender(i), Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'export the range as an excel
rng.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:="C:\Users\kread\Desktop\PDF split test" & CurLender(i)
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True

'MsgBox startcell
'MsgBox endcell


Next i






End Sub
 
Upvote 0
Thank you i think that would get at exactly what i was trying to do:

Specifically the HPageBreaks location is think is the element i was looking for.

Thanks!!
EndRow = ActiveSheet.HPageBreaks(PageNum).Location.Row - 1
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top