Excel VBA: Setting PageSetup.TopMargin for _EACH_ page

[breakfree]

Hello all,
This problem has been driving me crazy for hours. I have a simple excel
sheet with just 2 pages with about 50 rows. I want the first page to
have a special header which will take up a lot of space on the top. I
want the remaining pages without the header and normal page margins.
Since Excel does not provide a special "first page" setup, I am using a
macro that sets the header and the topmargin, prints the first page,
sets the header to blank and topmargin to a lesser value and prints the
rest of the pages. My code looks like this:

Dim sHeader as String
With wsSheet
sHeader = .PageSetup.CenterHeader
PageSetup.TopMargin = Application.InchesToPoints(2.5)
PrintOut From:=1, To:=1
PageSetup.CenterHeader = ""
PageSetup.HeaderMargin =
Application.InchesToPoints(1)
PrintOut From:=2
PageSetup.CenterHeader = sHeader
End With

What happens is really strange: when printing page 2, Excel thinks that
whole sheet has a top margin of 1 inch, and thus, prints nothing on the
second page, because if the top margin were 1 inch for page 1, all 50
rows would fit into 1 page and there is no second page. But that is not
true!!! It just printed page 1 with a topmargin of 2.5 inches so there
are more rows left to print.
I hope you understand my problem. Basically, I want to setup specific
topmargin values to _EACH_ page and it just isn't working.
Any help is appreciated,
Thanks!

 

[Jim Cone]

b,
Why not insert a manual pagebreak where you need it?
Jim Cone
San Francisco, USA


"breakfree"
<[email protected]>
wrote in message
Hello all,
This problem has been driving me crazy for hours. I have a simple excel
sheet with just 2 pages with about 50 rows. I want the first page to
have a special header which will take up a lot of space on the top. I
want the remaining pages without the header and normal page margins.
Since Excel does not provide a special "first page" setup, I am using a
macro that sets the header and the topmargin, prints the first page,
sets the header to blank and topmargin to a lesser value and prints the
rest of the pages. My code looks like this:

Dim sHeader as String
With wsSheet
sHeader = .PageSetup.CenterHeader
PageSetup.TopMargin = Application.InchesToPoints(2.5)
PrintOut From:=1, To:=1
PageSetup.CenterHeader = ""
PageSetup.HeaderMargin =
Application.InchesToPoints(1)
PrintOut From:=2
PageSetup.CenterHeader = sHeader
End With

What happens is really strange: when printing page 2, Excel thinks that
whole sheet has a top margin of 1 inch, and thus, prints nothing on the
second page, because if the top margin were 1 inch for page 1, all 50
rows would fit into 1 page and there is no second page. But that is not
true!!! It just printed page 1 with a topmargin of 2.5 inches so there
are more rows left to print.
I hope you understand my problem. Basically, I want to setup specific
topmargin values to _EACH_ page and it just isn't working.
Any help is appreciated,
Thanks!