MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Print Range

  Asked By: Klarissa    Date: Jan 02    Category: MS Office    Views: 1344

I have finance spreadsheets i.e. P&L's for different countries. Each
of the spreadsheets has a different last column which has data in it.
These reports are generated from our finance reporting tool. Instead
of manually setting a print range, is there a way of using VBA to set
the range which takes into account the last column with data. The last
row in all cases should be "710".[egs of print areas : .PrintArea
= "$A$1:$BM$710" ; .PrintArea = "$A$1:$BA$710"]
I don't know what the print range will be until the report has been
ran. I wish to attach the "macro" to the blank template which is used
by the reporting tool. When a country specific report is ran the
relevant columns will be "populated".



3 Answers Found

Answer #1    Answered By: Gina Tanaka     Answered On: Jan 02

Give this on e a shot:

Sub SetPrintAreaAllSheets()
For SheetIndex = 1 To ActiveWorkbook.Sheets.Count
vPrintrange = Sheets(SheetIndex).Cells(1, 1).CurrentRegion.Address
Sheets(SheetIndex).PageSetup.PrintArea = vPrintrange
Next SheetIndex
End Sub

Answer #2    Answered By: Mehr Malik     Answered On: Jan 02

I tried CurrentRegion but if you have lots of blank space in your region you get
the wrong answers.

I opted for the following: Thanks for the starting point

Sub SetPrintAreaAllSheets()
Dim vPrintrange As String
ReturnHome = ActiveSheet.Name 'To find your way home
For SheetIndex = 1 To ActiveWorkbook.Sheets.Count
Worksheets(SheetIndex).Select 'Have to select the sheet to set the
print area
LastCell = Sheets(SheetIndex).Cells.SpecialCells(xlLastCell).Address
'Find last cell
vPrintrange = Sheets(SheetIndex).Range(Range("A1"),
Range(LastCell)).Address 'Set your range
Sheets(SheetIndex).PageSetup.PrintArea = vPrintrange 'Set the print
Next SheetIndex'Next Tab
Worksheets(ReturnHome).Select ' Return to the sheet you started from
End Sub

Answer #3    Answered By: Nathaniel Martin     Answered On: Jan 02

A cleaner version is attainable since the range  is explicitly called by the
worksheet index. With the worksheet references, you don't have to 'change' to
each worksheet so you are saving process cycles from the screen refreshes that
would have taken place. You could also set ScreenUpdating to false & then back
to True; does the same thing! Anyway see if this version makes sense:

Sub SetPrintAreaAllSheets3()

Dim vPrintrange As String

For SheetIndex = 1 To ActiveWorkbook.Sheets.Count

vPrintrange = "A1:" & Sheets(SheetIndex).Cells.SpecialCells(xlLastCell).Address

Sheets(SheetIndex).PageSetup.PrintArea = vPrintrange 'Set the print  range

Next SheetIndex

End Sub

Didn't find what you were looking for? Find more on Print Range Or get search suggestion and latest updates.