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.
我正在将各种范围复制到新的Excel工作表,并在页面布局视图中查找引用下一页或任何特定页面的解决方案。
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?
我已经设置了页边距,边框和其他格式的页面布局,并希望根据屏幕上显示的布局填写我的报表。由于布局已设置,我可以硬编码单元格引用来放置我的范围,但我宁愿动态地确定它。有解决方案吗?
2 个解决方案
#1
3
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.
最简单的方法是更改为“页面布局”视图,然后使用LargeScroll转到所需的页面。
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.
“1”是您要执行的LargeScrolls的数量,因此在上面的示例中,您可以从任何地方向下移动1页。这将适用于页面布局视图中的任何页面。
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.
这是一个示例,如果您想转到第2页但不确定您的代码留给您的页面。它使用单元格(1,1)将您带到工作表的第一个单元格,该单元格将是第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).
您可以将“1”更改为任意数字。请记住,它的作用类似于偏移,所以如果你从单元格A1开始并想要转到第3页,你只会滚动2次,而不是3.代码看起来像ActiveWindow.LargeScroll 2因为它从当前页面下降了2 (2 + 1)。
#2
0
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).
要计算出高度,当您在代码中选择范围时,只需检查它的高度属性(范围(“A1”)。高度),它会告诉您下一个范围的Top属性将在何处放置。此外,您可以对标准高度进行硬编码(只需突出显示适合一页的单元格并转到即时窗口并键入?Selection.Height,您将获得标准高度)。
Hope this helps!
希望这可以帮助!
#1
3
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.
最简单的方法是更改为“页面布局”视图,然后使用LargeScroll转到所需的页面。
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.
“1”是您要执行的LargeScrolls的数量,因此在上面的示例中,您可以从任何地方向下移动1页。这将适用于页面布局视图中的任何页面。
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.
这是一个示例,如果您想转到第2页但不确定您的代码留给您的页面。它使用单元格(1,1)将您带到工作表的第一个单元格,该单元格将是第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).
您可以将“1”更改为任意数字。请记住,它的作用类似于偏移,所以如果你从单元格A1开始并想要转到第3页,你只会滚动2次,而不是3.代码看起来像ActiveWindow.LargeScroll 2因为它从当前页面下降了2 (2 + 1)。
#2
0
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).
要计算出高度,当您在代码中选择范围时,只需检查它的高度属性(范围(“A1”)。高度),它会告诉您下一个范围的Top属性将在何处放置。此外,您可以对标准高度进行硬编码(只需突出显示适合一页的单元格并转到即时窗口并键入?Selection.Height,您将获得标准高度)。
Hope this helps!
希望这可以帮助!