I have a macro that formats the printout for a report. It basically sets the page size, headers, footers, pagebreaks and ROWs to repeat.
However, last page of the report is summary page and doesn't need the Title block repeated on top.
Is there a VBA code that would allow me to select "Not to repeat the ROWs on last sheet"?
Thanks for any help I can get.
modytrane.
Last edited by modytrane; 11-15-2010 at 12:06 PM.
We'd really need to see the whole macro to understand the ramifications of your question. It sounds like there may be other code you DO still want running on the last sheet, too? Yes?
As you loop through sheets, you can test for a specific sheet to skip by name or by index position
Dim ws As Worksheet For each ws in Worksheets If ws.Name <> "Summary" Then 'your action code here End If Next ws For each ws in Worksheets If ws.Index <> Sheets.Count Then 'your action code here End If Next ws
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JB,
Thanks for you response.
The report is all from one worksheet.
I need to keep detailed data [size varies from project to project] and summary of that data on the same sheet.
Basically the data is copied from another sheet and then a macro generates the summary at the bottom.
When the user selects to print, the macro inserts pagebreaks and sets page sizes, Header, Footer etc.
It also iserts a pagebreak just before the summary so the summary prints on a seperate page.
All other pages [data portion] needs the title row repeated but the last page doesn't need it.
Follwing code sets up pagebreaks and sets print area:
This is the BeforePrint event:Worksheets("Estimate Rollup Summary").Cells.PageBreak = xlPageBreakNone Worksheets("Estimate Rollup summary").Rows(LR + 7).PageBreak = xlPageBreakManual ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & LR + 4 + z
Private Sub Workbook_BeforePrint(Cancel As Boolean) Application.ScreenUpdating = False With ActiveSheet.PageSetup .PrintTitleRows = "$6:$7" End With With ActiveSheet.PageSetup .LeftFooter = "&9Page &P of &N" .CenterFooter = "&D &T" .RightFooter = "&F" .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .Orientation = xlLandscape .PaperSize = xlPaper11x17 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 End With Application.ScreenUpdating = True End Sub
Thanks again for any help or suggestion you can provide.
modytrane.
JB,
I appologize for using wrong terminology earlier.
I recognize, the title says "last sheet".
I didn't mean last worsksheet, I meant last page of the report.
Again,
Sorry for wrong information.
modytrane.
There's code here to help determine how many pages will print in the current report.
http://www.ozgrid.com/VBA/printed-pages.htm
Once you have that number determined and stored in a variable (NumPages), print the report minus the last sheet:
ActiveSheet.PrintOut From:=1, To:=NumPages-1, Copies:=1, Collate:=True
Then remove your headers and print JUSTthe last page.
ActiveSheet.PrintOut From:=NumPages, To:=NumPages, Copies:=1, Collate:=True
Thanks JB,
modytrane
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks