Excel vba HPageBreak.Count doesn't work when macro is called from Auto_Open
I am working on a project where I read a CSV file, formats the data in a temp sheet ( OutputSheet
), copy-paste the formatted data to a printed sheet ( PrintSheet
), saves the PrintSheet as PDF after inserting manual page breaks so that the data block does not paginate. I am doing this in an Excel VBA macro function. Here's the relevant piece of code:
PrevPageNum = PrintSheet.HPageBreaks.Count
OutputSheet.Rows("6:" & (CurrHistoryRows + 14)).Copy
PrintSheet.Cells(PrtPstStRow, 1).PasteSpecial Paste:=xlPasteFormats
PrintSheet.Cells(PrtPstStRow, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
PageNum = PrintSheet.HPageBreaks.Count
If PrevPageNum < PageNum Then
PrintSheet.Rows(PrtPstStRow).PageBreak = xlPageBreakManual
End If
When I call the macro directly from code or using the assigned button on the Quick Access Toolbar, the manual page breaks are assigned correctly. But when I call this macro from Auto_Open, they are not assigned at all (everything else remains the same!). After debugging the code, I noticed that PrevPageNum
and are PageNum
always zero when the macro is called from Auto_Open
. In the other two cases, the values change.
I would also like to mention that it PrintSheet
has a width scaled to 1 Page
in the page layout.
Any ideas please! Thank.
Update:
I added these statements just before PrintSheet.HPageBreaks.Count
, and the page breaks started working with Auto_Open
.
OutputSheet.Activate
PrintSheet.Activate
FINAL Update:
When I ran this on a "slow" computer (processor - i3 with 3GB DDR2 RAM), then again I ran into the same page skipping problem. I eventually found a solution (or workaround) with 100% success. You need to scroll vertically to the last used line for it to be HPageBreaks Collection
updated with Count
. Final code:
PrintSheet.Activate CurrentRowNum = ActiveWindow.ScrollRow ActiveWindow.ScrollRow = 5000 'or the last used row PageNum = PrintSheet.HPageBreaks.Count ActiveWindow.ScrollRow = CurrentRowNum
source to share
When you use Auto_Open, there is no value because you didn't come from another page or even had a page by the time these variables were assigned.
You can have a built-in if statement to handle the case when they are null. Basically by handling the Auto_Open use case. Because in any other case, there will be value.
source to share