Hi SirJB7,
Thanks for the response.
In the above code, you mention about "From & to" to pick up the pages for printing.
However the situation here is when the excel sheet gets saved as PDF, the print area gets broken into multiple pages while it should come ideally as a single page. i.e. all the contents in the print area selected should come on 1 page.
Let me reword the problem statement.
Out friend has an excel file with 3 sheets:
Payroll
Payslip
List
Payroll sheet has a table containing the information like emp name, emp code, salary components etc.
Payslip sheet is formatted to look like a payslip. He had already used vlookup to get the details in the payslip from payroll sheet.
List sheet is where our friend wants the list of emp name, emp code and the file paths of the pdfs of the payslips.
In my first attempt, I was able to get all excep that the print area was getting broken into 4 pages (i.e. pdf file was showing to have 4 pages - breaking the contents).
If we to do it manually, then we have to adjust the vertical and horizontal pagebreaks to get all the contents in a single page and then do the save as pdf type to get the contents in a single page.
I tried recording the steps and i got the following code:
Range("B5:F54").Select
ActiveSheet.PageSetup.PrintArea = "$B$5:$F$54"
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.SmallScroll Down:=18
ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1
ActiveWindow.SmallScroll Down:=-33
ChDir "C:Usersamana001Desktop"
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:Usersamana001Desktop" & Range("D15").Value & ".pdf",Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False
Range("B3").Select
ActiveWindow.View = xlNormalView
then i faced these issues:
1. The above code works only if the sheet is new i.e. data copied into new sheet and macro is run.
2. Here the line - ActiveSheet.HPageBreaks(1).DragOff Direction:=xlDown, RegionIndex:=1 - gives me an error if i try to use macro again on the same sheet. If i comment it then the rest of the code works fine and i get a pdf file with only 1 page.
3. Then i made the changes and with the below code, i was able to get it number of pages in the pdf file down to 2.
newWB.ActiveSheet.PageSetup.PrintArea = "$B$5:$F$54"
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.SmallScroll down:=18
I am at a loss of how to code the manual adjustments for vertical & horizontal pagebreaks.
PS. Sorry if the description is too long.
