I am copying various ranges to a new Excel sheet, and looking for a solution to referencing the next page, or any specific page, while in Page Layout view.
I have already setup the page layout with margins, headers, and other formatting, and want to fill in my report based on the layout presented on screen. Since the layout is set, I can hard code the cell references to place my ranges, but I would rather determine this dynamically. Any solutions out there?
On
The easiest way to do this is to change to Page Layout view, then to use LargeScroll to go down to which ever page you want.
ActiveWindow.View = xlPageLayoutView '<--- Changes view to "Page Layout"
ActiveWindow.LargeScroll 1 '<--- Scrolls down a full page 1 time
The '1' is the number of LargeScrolls you want to execute so in the example above, you'd go down 1 page from wherever you are. This will work from any page in Page Layout View.
Here's an example for if you wanted to go to page 2 but were unsure what page your code left you on. It uses cells(1,1) to take you to the first cell of the worksheet which will be page 1.
Cells(1,1).Activate '<--- Takes you to first cell in your worksheet
ActiveWindow.View = xlPageLayoutView
ActiveWindow.LargeScroll 1
You can change the '1' to any number. Remember, it works like offset, so if you start in cell A1 and want to go to page 3, you would only scroll 2 times, not 3. The code would look like ActiveWindow.LargeScroll 2 because it's taking you down 2 from current page (2 + 1).
This is a tough one, but one suggestion could be that if you already have the layout set, then consider it your template page. There is a trick you can use to figure out if you'll "fall out of range," so-to-say. Before copying a new range to the template sheet, determine the height of the content you're copying and hold it against the "left-over" height of the destination (where the page will break). If it falls out of range, move it to the next page so you don't break up your ranges by the pages.
How would you know if it falls out of range? You can figure out a standard height per page when you start your code. Then decrement it as you paste on. This way will take care of different row heights you may have when copying/pasting.
To figure out the height, when you select the range in code, just check it's Height property (Range("A1").Height) and it will let you know where the next range's Top property will lay. Also, you could hard code the standard height (just highlight the cells that fit on one page and go to the immediate window and type ?Selection.Height and you'll have your standard height to work with).
Hope this helps!