Using an excel macro, how to reformat print area without saving? 2

[RyanScharfy]

I've got the following issue. I created a macro that creates a subtotaled spreadsheet that is about five pages long. There are no page breaks between subtotals. That same macro then copies that spreadsheet to other worksheets (tabs), and filters out certain rows of data so that each new report is less than a page long.

The problem is that each new tab, even though the report is shorter, still wants to print five pages (basically one page of data and 4 blank sheets).

Any idea on a quick fix, short of saving the spreadsheet? (Saving the spreadsheet fixes the problem). I'd like the users to be able to use the macro without having to save the report to their hard drive.

 

[Loomah]

Hi
This is one possible solution that relies on your data being contiguous and starting in cell A1. The code belongs in the Workbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
    .PrintArea = Range("A1").CurrentRegion.Address
End With
End Sub

See help for more information on the properties and methods available to the PageSetup object and about the PageSetup property.

Hope this helps
;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SmallCraig]

Ryan,

Since you clearly know the number of rows in each sheet, add some code to set the print area for each page:

With PageSetup

    .Printarea = "$A$1:$H$" & cstr(intLastRow)

'    etc.

End With

There are a lot of other page setup properties. If you record a macro that does any page setup stuff, you'll see all the groovy stuff that you can set. That said, this should give you what you're after. Incidentally, I chose "H" arbitrarily, assuming your number of columns will be constant - in my experience, you nearly always know how many columns you have - it's just the rows that vary!

Hope this helps,
SmallCraig

 

[RyanScharfy]

Thanks SmallCraig and Loomah,

I ended up using Loomah's only because some of my lists were larger than one sheet. Although it wasn't continguous from A1, it was contiguous from B7, and by putting the header in to be printed on every page, the whole thing came through, no problem. Thank you!

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$7"
End With
With ActiveSheet.PageSetup
.PrintArea = Range("B7&quot.CurrentRegion.Address
End With