How in vba do you find which cell will be on top left of page three if sheet
is printed.

Hi Sunil,

Investigate the .Location property of items in both the VPageBreaks and
HPageBreaks collections. For example ...

debug.Print activesheet.vpagebreaks(1).location.address

will return something along the lines of: $I$1

Please carefully note that "Page 3" depends on worksheet/print area size and
whether you have set to print it "Down, then Over" or "Over, then Down".

I'm sure you'll be able to adapt it to your specific purpose from there. (If
not, post back).

HTH, Sean

"Sunil Patel" wrote:

How in vba do you find which cell will be on top left of page three if sheet
is printed.

I have tried to insert this line (debug.Print
activesheet.vpagebreaks(1).location.address)
but i just get run time error 9 Subscript out of range.

Any ideas

"Sean Connolly" (DoNotSpam) wrote in
message ...
Hi Sunil,

Investigate the .Location property of items in both the VPageBreaks and
HPageBreaks collections. For example ...

debug.Print activesheet.vpagebreaks(1).location.address

will return something along the lines of: $I$1

Please carefully note that "Page 3" depends on worksheet/print area size
and
whether you have set to print it "Down, then Over" or "Over, then Down".

I'm sure you'll be able to adapt it to your specific purpose from there.
(If
not, post back).

HTH, Sean

"Sunil Patel" wrote:

How in vba do you find which cell will be on top left of page three if
sheet
is printed.

Hi Sunil,

Then we've discovered one thing at least. You obviously don't have any
vertical page breaks in the relevant worksheet! i.e. Your worksheet (or print
area) is equal to or less than 1 page wide.

(A quick question that will help me as much as you. How many pages will your
worksheet print onto with the print settings that you have? - Tip: File menu
| Print Preview).

By any chance, did you then try to enter the following into the Immediate
Pane of the Visual Basic Editor (VBE) ...

debug.Print activesheet.hpagebreaks(1).location.address

Hit enter and, if your worksheet/print area exceeds 1 page (long), the
address of the 1st cell of the second page (down) will be returned.

(If it doesn't then you get the 'Subscript out of Range' error, because the
collection item that you're trying to refer to doesn't exist).

Hope that's clearer and helps and trust that you can go on from there.

Cheers, Sean.

"Sunil Patel" wrote:

I have tried to insert this line (debug.Print
activesheet.vpagebreaks(1).location.address)
but i just get run time error 9 Subscript out of range.

Any ideas

"Sean Connolly" (DoNotSpam) wrote in
message ...
Hi Sunil,

Investigate the .Location property of items in both the VPageBreaks and
HPageBreaks collections. For example ...

debug.Print activesheet.vpagebreaks(1).location.address

will return something along the lines of: $I$1

Please carefully note that "Page 3" depends on worksheet/print area size
and
whether you have set to print it "Down, then Over" or "Over, then Down".

I'm sure you'll be able to adapt it to your specific purpose from there.
(If
not, post back).

HTH, Sean

"Sunil Patel" wrote:

How in vba do you find which cell will be on top left of page three if
sheet
is printed.

Hi Sunil,

Then we've discovered one thing at least. You obviously don't have any
vertical page breaks in the relevant worksheet! i.e. Your worksheet (or print
area) is equal to or less than 1 page wide.

(A quick question that will help me as much as you. How many pages will your
worksheet print onto with the print settings that you have? - Tip: File menu
| Print Preview).

By any chance, did you then try to enter the following into the Immediate
Pane of the Visual Basic Editor (VBE) ...

debug.Print activesheet.hpagebreaks(1).location.address

Hit enter and, if your worksheet/print area exceeds 1 page (long), the
address of the 1st cell of the second page (down) will be returned.

(If it doesn't then you get the 'Subscript out of Range' error, because the
collection item that you're trying to refer to doesn't exist).

Hope that's clearer and helps and trust that you can go on from there.

Cheers, Sean.

"Sunil Patel" wrote:

I have tried to insert this line (debug.Print
activesheet.vpagebreaks(1).location.address)
but i just get run time error 9 Subscript out of range.

Any ideas

"Sean Connolly" (DoNotSpam) wrote in
message ...
Hi Sunil,

Investigate the .Location property of items in both the VPageBreaks and
HPageBreaks collections. For example ...

debug.Print activesheet.vpagebreaks(1).location.address

will return something along the lines of: $I$1

Please carefully note that "Page 3" depends on worksheet/print area size
and
whether you have set to print it "Down, then Over" or "Over, then Down".

I'm sure you'll be able to adapt it to your specific purpose from there.
(If
not, post back).

HTH, Sean

"Sunil Patel" wrote:

How in vba do you find which cell will be on top left of page three if
sheet
is printed.