Resize all worksheets in workbook to fit to one page (for printing purposes)?

N

NeilATaylor

Board Regular
Joined
Aug 7, 2007
Messages
185
Hi All,

I have a workbook, which has numerous worksheets. On each worksheet is a screen print, not excel data but a CTRL+print Screen of another system. All are the same size. I want to print the whole workbook, each worksheet on one page, but at the moment the only way I know how to do this is to go into each worksheet and go File>Page Setup>Fit to 1 page tall by 1 page wide etc... is there a way to apply this change to the whole workbook, without going through each worksheet one by one??

Thanks
Neil
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Try

Code: 
Sub PrtSetUp()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next ws
End Sub
 
Upvote 0
Click on the first worksheet tab at the bottom of the screen so that it is active. With the first tab still active, click on the button to the left of the first tab that will take you to the last worksheet.

Hold down the shift key, and click on the last worksheet tab. This should make all of the tabs become active (i.e. they will all be the same color).

Click on the File, Page Setup and then set your print to fit parameters as if you were doing so for just one tab.

I believe this will update all worksheets in the workbook.

hth,
colbymack
 
Upvote 0
Thanks colbymack that does seem to work!
A macro would be useful, however I don't think that one worked VoGII.
Did it work when you tested it?
Thanks
 
Upvote 0
Sorry, I only checked that the code compiled. Try

Code: 
Sub PrtSetUp()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With ws.PageSetup
        .PrintArea = ws.UsedRange.Address
        .PaperSize = xlPaperA4 'If in the USA use .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
Next ws
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
PDF Create button to manage output formatting
Replies
0
Views
718
grabrail
G
C
  • Question
Multiple Workbooks to one.
2
Replies
11
Views
557
dmt32
D
M
  • Solved
Fit Columns to page in printer settings isn't working right
Replies
2
Views
446
Mr_Phil
M
D
  • Solved
VBA Macro to consolidate all info on only selected tabs
Replies
2
Views
310
Dilligas915
D
N
  • Locked
  • Question
Help are these correct answers any experts new to excel
Replies
1
Views
613
Joe4
Joe4

Forum statistics

Threads
1,215,032
Messages
6,122,770
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top