Excel Print same area on several sheets

[srogers]

I'm close to what I want but print preview shows it will only print the cell on each sheet where my cursor is sitting.

Here's what I have:

Sub PrintSalesLabor()
Dim vArrSh As Variant
Dim sh As Long

vArrSh = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O")
For sh = LBound(vArrSh) To UBound(vArrSh)

With Worksheets(vArrSh(sh)).PageSetup
.PrintArea = Range("A1").CurrentRegion.Address
End With

Next sh
Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O")).Select
SendKeys ("^p%n"), True
End Sub


After running this I look at each sheet and the dotted line surrounds what I want to print(but the area is not highlighted). Each sheet is selected and the print dialog box is on the screen. Then I choose Preview and on each page I only see whatever contents are in the cell where I left my cursor.

What am I missing?
Thanks-
Sharon

 

[SkipVought]

Sharon,

I thinks its the Range Object Object (sic)

        With Worksheets(vArrSh(sh))
            .PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
        End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[srogers]

I inserted that and commented mine out but it still print previews just the date (if the cursor is sitting on one of the date cells) on each page.
Do I need to actually select the range somehow?

Sub PrintSalesLabor()

Dim vArrSh As Variant
Dim sh As Long

vArrSh = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O")
For sh = LBound(vArrSh) To UBound(vArrSh)

With Worksheets(vArrSh(sh))
.PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
End With

Next sh
Sheets(Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O")).Select
SendKeys ("^p%n"), True
End Sub

Thank you.

 

[Bowers74]

Forgive me . . . but what EXACTLY are you trying to do?

Do you want to have the same "PrintArea" set for each sheet in your Workbook?



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[srogers]

Yes. They like having one department on each sheet and weekly figures are on each row with a total at the end of each period/month. So each sheet looks the same - so the same PrintArea on each sheet is what I need.
Thanks-
Sharon

 

[Bowers74]

Would something like this work for you:

Sub SetPrintArea()
Dim ws As Worksheet, PrintRange As String
[COLOR=green]' Set PrintRange to the UsedRange on 1st Sheet[/color]
PrintRange = Sheets(1).UsedRange.Address
For Each ws In ActiveWorkbook.Worksheets
    ws.PageSetup.PrintArea = PrintRange
Next ws
ActiveWorkbook.Worksheets.PrintPreview
End Sub

Since you said that ALL of the worksheets look the same and that you needed to PrintArea to be the same for ALL of the Worksheets, I set the PrintArea to the UsedRange of the first Worksheet. You don't need to have an array, if you want to select all of the worksheets for PrintPreview either.

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[srogers]

We're getting there. The problem now is that there are about 4 blank sheets inbetween each department.
After I run the procedure I looked at the worksheet and the dotted line is too wide by one column and includes about 30 blank rows (and then there is a second dotted line 6 rows below that).

I tried setting the print area and saving it but when I run the procedure it goes right back.
Maybe there is something else I need to clear?

Thanks for your help.

 

[Bowers74]

Please give me a little bit more detail as to how the Workbook is set up, and then explain how EACH Worksheet is set up (i.e. do ANY differ from the 1st).

When you "update" the data, are cells erased, or deleted completely?



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[srogers]

Ok.
I have sheets "A" thru "O". Each sheet is used for one department.

On "A" I have a title on row 1, then 3 blank rows, row 5 is the name of the department and row 6 are the column names like Date, Comments, Payroll dollars, etc. (there are 8 columns A thru H).
Data starts on row 7.

There are 4 text boxes on this sheet also. One is for an explanation and the other three are assigned macros. I set the properties of the text box not to print.

(One row of data gets added to each of the sheets each week. Another workbook has that data in it and so I put the macro in that workbook - they run it, and it adds one row of data to the workbook we're trying to print.)

The 4 text boxes assigned macros: one adds formulas to the row when they are ready to add a summary. (what they do is add one row of data each week and after 4 weeks they add the formulas to the next row - I thought if I hardcoded the formulas in the cells I might have printing problems. Didn't realize not printing blank space would be such a challenge for me.)
One other macro is used just at the beginning of the year to delete all the data, formulas and formatting (underlines).
The last macro is the one I'm working on now for printing.

Each of the other worksheets is the same without any text boxes. Row 1 is the title, rows 2, 3, 4 are blank and row 5 has the department name, row 6 the column headings and the data starts on row 7.

After running the procedure - code below - the print area shows it is set to: A1:R107

I read a tip at:

http://www.exceltip.com/show_tip/Ex...column_or_columns_in_Microsoft_Excel/204.html

that said to avoid printing the empty columns/rows you need to set scaling to 1 page wide by 1 tall. I did that and the print area changed - it didn't include the extra columns. But it automatically changed the "Adjust to" percent to 47 (we need it bigger than that).

So that's where I am. Thanks for looking at this.
Sharon

Sub SetPrintArea()
Dim ws As Worksheet
Dim PrintRange As String
' Set PrintRange to the UsedRange on 1st Sheet
PrintRange = Sheets("A").UsedRange.Address
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PrintArea = PrintRange
Next ws
ActiveWorkbook.Worksheets.PrintPreview

End Sub

 

[srogers]

Maybe I could find the last row and somehow use that to set the range?

Thanks,
Sharon

 

[srogers]

I know this code is not right but how about the concept of using the last row to set the printarea?

If it sounds like I am on the right track - can you help me get the syntax right? The lines in question have the two ** in front of it.

Thanks,
Sharon

Sub PrintMe()
Dim LastRow As Long

LastRow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

**Worksheets("A").PageSetup.PrintArea = "$A$1:" & LastRow
**PrintRange = Sheets("A").UsedRange.Address

For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.PrintArea = PrintRange
Next ws

ActiveWorkbook.Worksheets.PrintPreview

End Sub

 

[Tiglet]

Hi,

Haven't got Excel on this machine so can't check it out but could you not go to each sheet in turn and select a1, having previously put a full stop (in white font) in column 1 cells for each of the blank rows then just do currentregion.select and use that to setprintarea. It's a bit of a cheat but it should do the job and will dynamically pick up the additional rows as they are added

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[srogers]

Thanks for your reply.
I'm not sure what a full stop is.
Will that idea also delete rows at the beginning of the next year when they delete the data and start over?
Thank you -
Sharon

 

[Tiglet]

A full stop is just .
All it does is put something into the blank rows so that you have one block of data without any blanks. I suggested making the font white so they don't print (always assuming you are printing onto white paper!) currentregion.select will then work. The current region will only select in all directions from the active cell up to the first blank cell or edge of the spreadsheet (ie column a, row 65536)

I'm not sure what you mean about deleting data. If having the blank rows is just for presentation, then increase the height of the row and change the vertical position of the text in the header row. You can then delete the blank rows but for printing it will still look the same. This will avoid the problem of having full stops.

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[srogers]

Thanks - I'll try that tomorrow.

What I mean about deleting is that they add one row of data every week until the end of the year. Then before the next year starts they delete everything but the column headings so they have a blank slate to start the new year with.
So the first week they will have and want to print one row of data - so I want the printarea to shrink back down to one page (by the end of the year it will take two pages to print each worksheet).

Thanks-

 

[foundryqa]

Sharon,

Remove the two lines of code you have with the ** notation and add the following:

Worksheets("A").PageSetup.PrintArea = Range(Cells _
(1, 1), Cells(LastRow, 8)).Address 'The 8 is for column H

That should do the trick.

Fred

 

[Tiglet]

srogers,

Cutrrentregion will only be as big as the data, so if there is only one line of data + the headings that's what you'll get and similarly if there are 52 lines of data + headings you'll get that.

Tiglet

Living on Earth is expensive, but it does include a free trip around the sun every year

 

[srogers]

Tiglet - Thank you - that sounds like it will work then, I just haven't had time to try it yet - but will.

Fred - Thank you - I still had some problems after I tried it - but apparently it had something to do with my text boxes I had assigned to macros (even though their properties are set not to print - and they didn't print). The print area was going way over to column Q. So I deleted the boxes and the columns and saved the file and closed and re-opened and then added some textboxes back to it and then your solution worked.

Thanks -
Sharon