Suggestions on how to test if wrapped text cell has moved into page 2? 1

[SBDev]

Hi everyone,

I have a worksheet that contains (among other columns) a description column. Each description cell in this column wraps its' text. I would like to test for the condition when the text in this cell exceeds that which can fit on page one and subsequently moves to page two.

If this condition is true I want to copy the header row that is atop page one to the second page.

This scenario would repeat every time a page's height is exceeded by wrapped text.

Your suggestions would be welcomed.

Truly,
Steve

 

[RobBroekhuis]

Steve,
Two suggestions:

- to check if an automatic pagebreak has been generated above the current cell, use
if activecell.entirerow.pagebreak=xlPageBreakManual then ...

- but also be sure to check out Excel's automatic header rows feature (in File-Page Setup, Sheet tab, "rows to repeat at top"

Rob

 

[SBDev]

Hi Rob (and everyone),

Rob thank you for your post. Your second suggestion would require less work and is a good fit for the requirements; however, It doesn't seem to work.

I have a row A7:M7 that I would like to repeat at the top of all subsequent pages. When I place this in the Rows to repeat in the Sheet Tab it replaces it with $7:$7, and when I attempt to place data in column A that extends beyond page 1, a page is not inserted. Perhaps my worksheet is not set-up correctly?

I may have to use your first suggestion, and with it have 'switch copying' logic programed, where If on page one or two then copy cells A7:M7 and paste on A?:M?; otherwise If page three or four then copy cells A7:M7 and past to to N1:Z1.

I appreciate your input, thank you!

Truly,
Steve

 

[RobBroekhuis]

You mean you have seven rows you are trying to repeat? Then just fill in the "rows to repeat" field with $1:$7
It should work just fine (you'll only see it in print preview or when you print, not on the Excel grid itself).
Rob

 

[SBDev]

Hi Rob (and everyone),

Your last post realy seals the decision, as I need to have an active button copied when the

if activecell.entirerow.pagebreak=xlPageBreakManual then...

condition is met.

Thank you.