Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Macro For Multiple Sheet Print Areas

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need a macro to set the same print area over approx 50 sheets.
Sheet 1, has the print area I want to replicate.
The print area is A1:U190, the sheet needs to be printed on 1 page wide, by two pages tall. It must also page break at row 79.

The sheets are say named Sheet 1 to Sheet 50.

I am using excel 2007.

Any suggestions welcome.


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Print The Current Worksheet in Excel
- This free Excel macro will print the current active worksheet in Excel. This means that whatever sheet you are currentl
Print all Embedded Charts in the Entire Workbook
- This macro will print every embedded chart in the active workbook. If you have an embedded chart in any worksheet within

Similar Topics

I have a sheet set up with a print area 1 page tall by 5 pages wide.

Is there a way to make certain pages from the print area print dependent on if a cell is blank or not?

For example: If Cell A of Sheet 1 is not blank, print Page 1 of Sheet 2. If Cell B of Sheet 2 is not blank, print Page 2 of Sheet 2, and so on. Additionally, Page 1 and 5 of Sheet 2 always need to be printed.

Is there a function or VB code that can be written to accomplish this?

Hi Guys,

Is it possible to have have multiple set print areas on the same sheet?

1) The reason being is on the same sheet i might want to just have 2 sets of information printed out e.g (say ranges a1:a26 and k1:k100) (Each set print area needs to be Printed on its own sheet), therefore if i could have multiple set print areas on regardless of same sheet or not, then hopefully i will be able to print certain data that i need.

2) Is there a keyboard shortcut or something that i can do to highlight all selected print areas?

3) What is the VBA code to print all set print areas? All set print areas should be printed on its own sheet fitted prefferably on 1 sheet.


I need a macro so that I can set multiple print areas on a work sheet. It also needs to send me to a print preview screen before printing to make sure it looks accurate.

I need to be able to put these things in to adjust the print area:
1. Starting position
2. Width (columns)
3. Height (rows)
4. Horizontal/Vertical
5. Number of pages
# wide
# tall
6. Print Preview
7. Actually print what I set above, I wrote one that would look correctly on the preview, but printed incorrectly.

Another thing, a lot of the times Column A will need to be in every print out on a specific page. So I may need Column A plus Column B-S on one print, then Column A and Columns T-AG on another. That can get tricky.

any suggestions with part of this code?

Hope you folks can help me out with a strange one.

I have several worksheets formatted in exactly the same way as follows:

Col A - width 4
Col B - hidden
Col C - width 4
Col D - Width 108
Col E - Width 3
Col F - Width 11
Col G - Hidden
Col H - Width 11 & Empty

My print range should be Cols A:G (I have used page setup to set the
scaling to fit 1 page wide by [blank] pages tall, thus each sheet will
print as many pages as required depending on number of rows]

When I have the print range set to A:G only columns A:E show on the
print preview (and also on the actual print out) and when I make print
area A:F only A:C show on the print preview.

To get A:G printed I have to set the print area to A:H.

In all 3 of the examples above when I look at the print preview there
is a blank area on the right hand side of the page where the missing
columns SHOULD be but aren't... ie the size of the page seems to be
formatted correctly to include all the columns i want but they just
don't print unless 1 extra column is selected in the print area.

Has anyone out there experienced this problem before?
Thanks in advance.



Just a quick one regarding the print area. I have a worksheet and I have specified the are I want to print off via the 'Set Print Area'. I then go into the print preview and it defualts to, say. 50 sheets - at this point everything is fine! However, when I go to modify the print preview to, say, 1 page wide by 10 tall (which should be possible given the sze of what i want to print) it reverts to around 17,000 sheets and then the warning box saying 'Margins Do Not Fit page Size'!

I have tried copying the info to another workbook - doesn't work!

Anybody have any ideas?



I want to print two different formats on one page without having to reload the page in the printer. I have two page formats on one sheet. The print areas that have different row heights and column widths. Currently I print one print area and then load the page into the printer so I can change the print area to the other format to print the remainder of a page.

Is it possible using VBA to print one part of a page, prevent a page break so I can change the format of the print area and then continue to print the remainder of the page from a different print area which contains a different row/column format than what I printed on the top of the page?

If this is possible can you outline the steps needed to accomplish this task or point me to a discussion on how to do this? One thing I don't know how to do is prevent the page break. When I print one area it finishes part of the page and ejects the page. I need to stop the printer from ejecting the page so I can change the format for the remainder of the page.


I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%.
I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.

i am having trouble getting selected print area (the areas is from b2:aq44) to print as i'd like. after i selected the area i went to print preview and choose fit to 1page wide by 1 page tall. but the table remains shrunken at the top of the page. is there a way to have the data take up all the available space when fitting the print area to a 1 page printout?

thanks - brian

I have a worksheet with blank tables on 2 template tabs named viz: ON BOARD and NOT ON BOARD, each with 3 sheets to print when viewed on print preview.

What I want is just print ONLY those sheets which have data filled in., i.e if I have data on 1 sheet out of 3 sheets, I want only 1 page to be printed, if there is data on 2 sheets then print only 2 and so on, from both the sheet tabs.

On Page Set up window, I have following specs:

On Page Tab: As follows

Orientation: Landscape

Adjust to: 75 % of normal size

Paper size: 8.5 X 14" Legal

On Sheet Tab:

Print Area A1:O51

Under Print titles:

Rows to repeat at top: $1:$6

These settings are standard for both the sheet tabs.

VBA code OR MACRO required. Thanks.

Hey guys,
I have a spreadsheet that sets up a print area by a commandbutton and I can't get it to work correctly. The area I am trying to print fits within the print area, but when I preview it keeps cutting off the last row and placing it on the next page. If I select

Fit to 2 pages wide by 1 pages tall

it works, this also gives me an auto-generated 92% in the fit to above it (print setup options) So I tried it at 90%, 80% even as low as 50%, no matter what I do print preview cuts off the bottom row and places it on another page and I dont know why. I cannot use the "Fit to # pages wide by 1pages tall" because my spreadsheet keeps growing.

Does anyone know why this is happening?

BTW As an interesting side note, I have a simular spreadsheet that uses the same code, it is the same size in height and width, and is the exact same in every way minus the information in the cells. This one works flawlessly with the commandbutton using the code to set it at 90%

I having some trouble with page breaks I hope someone can help me with a solution. I have a spreadsheet that has certain pictures on each sheet. With each picture i have a button with the following macro (that was given to me by Datsmart in this forum and it works awesome, just like I wanted it too!!)

Sub CopyPrintArea()
area = ActiveSheet.PageSetup.PRINTAREA
LR = Sheets("Print List").UsedRange.Rows.Count + 1
Range(area).Copy Sheets("Print List").Range("B" & LR)
End Sub

The macro copies the print area to another sheet so that all selected pictures may be printed together. The problem that i am running into is that the pictures overlap over page breaks and are split when they print. What is the best way to avoid this happening? Is there a way to bump the picture down below the page break automatically? Any help would be appreciated!

Is there a way to print content from different sheets in a workbook on a single sheet of paper? For example, the print area of sheet 1 on the top half of the page and the print area of sheet 2 on the bottom half.

I know I could aggregate the content on one sheet in various ways, but it's more of a formatting issue -- I want to use different column widths and row heights for each print area.

Greetings Board

Windows XP Pro; Excel 2003

I'm running some vba that lets the user select certain columns to hide and unhide. The columns may be contiguous or they may not be. Could someone please help me to set up my print area and page breaks?

For example, let's say I'm on Sheet 1, and want to print two pages. A1:L20 and M1:X20.
Then let's say (in another instance) I'm still on Sheet 1, and again want to print two pages. A1:L20 for page one, and X1:AI20 for page two - and I don't want a blank sheet to print for M:X.

Some sample VBA please?

MEGA THANK YOU as always.

I have a sheet that will be about 5 pages long if printed. To minimize wasted ink and paper, using VB I would like to be able to have it only print the portion uses (data entered) along with a section at the top and bottom. The section at the top would print on the top of the first page and the section at the bottom would print on the last page.

I know I could record a macro to select an area and print that but it would not do what I am trying to accomplish. Any ideas?

In my macro, I loop through my sheet at 35 row intervals looking for a non null if column A. If I find a null in column A, I back up a row until i find a non null in column A. Once found, I set a page break. I then increment another 35 rows and do the same test.

The logic seems to work well excpet that current print area is getting in the way and I can't seem to clear it. I do not know how to clear the print area in the macro (if someone knows how that would be great) however, I can't get the print area to clear even if I do a "Clear Print Area" under Page Layout of the Toolbar Ribbon (Excel 2007)

Can someone help?


I have a excel 2007 file that contains 79 worksheets w/ all same formatting.
I have already selected all worksheets and set the page layout to size A3. Now, i need to set the print area to A1:N58 for all 79 sheets and print them out. I only know how to do this for 1 worksheet (activesheet) at a time. Below is what i used.

Sub test3()
' test3 Macro

ActiveSheet.PageSetup.PrintArea = "$A$1:$N$58"
End Sub

How do I apply the print area to all 79 sheets and then have all 79 worksheets printed at once?

Hi, I've had this problem with excel a few times now and can't get to the bottom of it.

I have a worksheet, its in landscape and it will print on one page if I reduce its size to 65%, so its not huge or anything. However when I use page setup and choose to make the sheet 1 page wide and 1 page tall, excel reduces its size to 50% and makes it squashes it towards the top left. Its still readable, but its annoying and it just looks out of place with the other printouts I have in a folder.

I have deleting all cells to the right and bottom of the data on the sheet. I have also resetted the print area in View>page break preview, so in page break preview it only shows my data bordered by blue. Also I have cleared the print area in the "File" menu.

Does anyone one know why this is happening? Or can think of anything else for me to check?

I know that my spreadsheet will fit on one sheet wide (and maybe several pages tall). I have written (based on recording) a macro to do a page setup. Part of the code is....

With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = False
End With

There are other statements within the "with" statement not listed here, serving other formatting needs. The problem is... the code listed above does not seem to work ! In other words, the formatting does not setup the print area for one sheet wide and unlimited sheets tall. Can anyone offer advice why this is not working? I did a Mr.Excel search for "fit to one page", but did not find my answer - so sorry if this is a repeated question. Thanks very much for your help.

I have a workbook with the print area spanning several pages across numerous
worksheets. I want to remove the first page from the print order.

In "page break preview" I try to drag the lines so that the print area
closes down, but it re-sizes to the original frame.

How do I take this page off the print area? Many thanks!

I have a macro that formats a sheet. On my PC the print area seems fine with no columns flowing over to an additional page. However, when I use the macro on a different PC the print areas are different with maybe a row flowing on to a new page which I don't want. The Macro outputs the data in the same columns with the amount rows used being different depending on the amount of data used.

Is it possible to set the amount of columns to print via code? I would like mine to print columns A through Q for example.

Also, Is there a way to adjust the print area by the actual rows being used? I don't want a bunch of blank pages without any data on them in the print job.

Brian I have a client freakingo out, becuase everything a PIVOT table is updated the page breaks and print area does not reset. So if one product is chosen rather than all it prints extra blank pages.

I have followed all of the excel help and have gone to the last cell and deleted all cells up to my data, then copied and pasted it into a new worksheet.

(note: I have about 15 tabs this is happeneing to)

I have also reset print areas, reset page breaks, set the print area to the whole list.

Is there a way to have it automatically detect what cells have data and set the page breaks and print area to that. When I reset the print area it keeps selecting way more cells than needed!

Please help.

Hello experts,

I am using Excel XP and have a problem with a macro which prints a sheet 3 times. I use Excel to print Bill of ladings, and therefor have to print the same sheet 3 times.
I do this with a simple macro with the following code:

Sub Afdrukken()
ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True
End Sub

(Afdrukken is dutch for Print)

This works fine, but the printout is only a small part of the page, so i have specified a print area. The first page comes out perfectly. On the 2 copies however a frame is printed around the text (i think this is the print area frame). The option to print guidlines is off, is there something i miss here?

Thanks for any help,

Emiel Schreur

I have a sheet setup with 6 identical A4 pages on it spread horizontally. The title pains across the top and left hand side are frozen. The pages span from left to right and a user would fill in each page sequentially left to right. A user may only wish to use say 2 of these pages on the sheet, is there any way I can make Excel change the print area so that it only prints these two pages. Basically I would like to set it up so that if text is entered into a specific cell on a page then it will set the print area to print that page.

Hi, I'm trying to make a macro to print out a sheet thats too wide for one printout.

Therefore, it should print the sheet to 3 pages (landscaped) but with each page having column A.

So, I have 4 sections/columns:

A - B - C - D

The print out would be:

Page 1 - A & B
Page 2 - A & C
Page 3 - A & D

Is that possible?


I have a workbook with 5 sheets. These sheets generally span 1.5 pages wide and 3 pages long in automatic height & width (scale to fit = 100%).

Now, I just did something and the following phenomenon appears:
1) There is no problem with automatic height and length, scale to fit = 100%
2) As soon as I set height = some page(s), or width = some page(s), Excel divides my sheet into as many pages as there are cells. At the same time, the "scale to fit" percentage is automatically reduced to 10%.
3) I have tried to reset the print area but that just adds more cells into my print area (each of them still print in one page)
4) The only way to restore the print preview to a "normal" state is by setting the height and width values to "automatic" and scale-to-fit percentage to 100%. But of course, by doing so I cannot print my sheets in the number of pages I want.

Any advice is greatly appreciated!