Excel VBA: Setting PageSetup.TopMargin for _EACH_ page

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!

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


"breakfree"
<[email protected]>
wrote in message
news:[email protected]
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!


--
breakfree
------------------------------------------------------------------------
breakfree's Profile: http://www.excelforum.com/member.php...o&userid=27806
View this thread: http://www.excelforum.com/showthread...hreadid=473145