4

I am working on a project wherein I read a CSV file, format data in a temporary sheet (OutputSheet), copy-paste the formatted data on a sheet to be printed (PrintSheet), save the PrintSheet as PDF after inserting manual page breaks so that a block of data is not split across pages. I do this in an Excel VBA Macro function. Here is the relevant part of the 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 within the code or from an assigned button in Quick Access toolbar the Manual Page breaks are assigned correctly. But when I call this Macro from Auto_Open these are not assigned at all (everything else remaining the same!). Upon debugging the code, I noticed that PrevPageNum and PageNum are always zero when the macro is called from Auto_Open. In other two case the values change.

Also would like to mention that PrintSheet has width scaled to 1 Page in the Page Layout.

Any ideas please! Thank you.

Update: I added these statements just before PrintSheet.HPageBreaks.Count and the Page Breaks started working from Auto_Open as well.

OutputSheet.Activate
PrintSheet.Activate

FINAL Update: When I ran this on a "slow" computer (processor - i3 with 3GB DDR2 RAM) then again I faced the same issue of Page Breaks being skipped. After much looking around finally I found a solution (or a workaround) having 100% success rate. You need to scroll vertically to the last used row so that HPageBreaks Collection gets refreshed with the Count. Final Code as follows:

PrintSheet.Activate
CurrentRowNum = ActiveWindow.ScrollRow
ActiveWindow.ScrollRow = 5000 'or the last used row
PageNum = PrintSheet.HPageBreaks.Count
ActiveWindow.ScrollRow = CurrentRowNum
Community
  • 1
  • 1
NP3
  • 652
  • 8
  • 21
  • Sorry to say this seems to be specific to Excel-2013. I tested this scenario in Excel-2010 and Auto_Open reports page breaks correctly. Clutching at straws, it might be worth creating a test case with _only_ the page break stuff in case some other part of your code is interacting with this. – chris neilsen Oct 26 '14 at 06:57
  • I tested the whole project in Excel 2010 on another machine and it went perfectly fine. I then created another macro-enabled workbook with only the relevant portion of the code and tested in, both, Excel 2013 and 2010 and this was also successful. So there was something in the code, which Excel 2013 did not like. I then followed PJ Rosenburg's (below) approach and voila! It worked! – NP3 Oct 26 '14 at 09:55

1 Answers1

1

When you are using Auto_Open, there is no value because you weren't coming from another page, or even have a page by the time those variables are assigned.

You could have an if statement built in to handle the case that those are both zero. Basically, handling the case that you use Auto_Open. Because in any other case, there will be a value there.

peege
  • 2,467
  • 1
  • 10
  • 24
  • So are you saying, in effect, that in Auto_Open case I can't use `PrintSheet.HPageBreaks.Count`? If yes then what are the alternatives (in the IF statement)? I tried (with no success) using `DoEvents`. I also tried using `PrintSheet.Activate` just before the `PrintSheet.HPageBreaks.Count` statement. – NP3 Oct 26 '14 at 06:49
  • Perhaps you could simulate opening a page and then switching back to the other page/sheet, just to get a value for that. Or, if you know what the variables should be set to, just set them. I don't have any way of testing it right now. – peege Oct 26 '14 at 08:46
  • Thanks for the suggestion. I Activated the Output sheet, then activated the Print sheet just before calling `PrintSheet.HPageBreaks.Count` and there it is! Now, it works from within Auto_Open as well! I have updated my question as well. – NP3 Oct 26 '14 at 09:57
  • Great. Glad to hear it. There might be some more practical ways to accomplish this, but a working solution > non-working solution. – peege Oct 26 '14 at 20:39
  • FYI please: check my solution in the updated question. – NP3 Nov 21 '14 at 06:37