Issues with pagination in a worksheet generated by a macro

[anindyab91]

Hello everyone,


I am trying to write a macro that will generate a new worksheet with 2 pages of A4 size in landscape orientation from a set of data. This new worksheet will be in turn converted to pdf and e-mailed/ printed. The pages need to have fixed margins and header/footer sizes. Here's the code I am using:

With printReport.PageSetup
    .PaperSize = 9 'xlPaperA4
    .Orientation = 2 'xlLandscape
    .LeftMargin = Application.InchesToPoints(0.2)
    .RightMargin = Application.InchesToPoints(0.2)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.25)
    .FooterMargin = Application.InchesToPoints(0.25)
End With

Also, I am using the command for hardcoded vertical page breaks instead of "fit to pages" property:

Set ActiveSheet.VPageBreaks(1).Location = Range(pageBreakLocation)

The number of columns in each page is dependent on the set of data. To fit all the columns in only 2 pages, I am calculating the optimal column width and resizing the columns. Please find the code below:

AvailablePageWidth = Application.InchesToPoints(11.7) - (printReport.PageSetup.LeftMargin + printReport.PageSetup.RightMargin)
OptimalColumnWidth =  AvailablePageWidth / noOfColumnsinaPage
printReport.Range(printReport.Cells(1, 1), printReport.Cells(1, lastColumn)).Select
            selection.ColumnWidth = OptimalColumnWidth

Now the macro is running as expected on my version of excel (version 2016). However when I tried to run the same macro in another system with version 2013, the columns are overflowing from one page to another (i.e. last 2/3 columns from 1st page overflows into 2nd page, the page that was supposed to be 2nd page is starting as a third page with 2/3 columns from that page overflowing into the next page (now page# 4), making the new worksheet contain 4 pages in total.


Even though the size of the page and columns along with margins are mentioned explicitly in the macro, please can someone help me fix this issue?


Appreciate your help. Thanks!