If you figure that you may use the macro quite a bit, in a number of different workbooks, or if you figure that you may need to change the print area regularly, you could change the macro so that it prompts the user for a range to use:
You can also enter the more areas range in the code if you want. The macro will add a new sheet and copy all the selection areas on it.
Print selection or range with one or more areas with a macro. Go back and do the same for the other areas. To choose a different print area for your needs, replace the range that is assigned to the sPrintArea variable. Excel 2010-2013: Choose the Linked picture button. This month I’ll show you how you can choose rows or columns to appear on every page when you print. If you prefer to have the print area set to some range that you specify, rather than needing to set the print area on the active worksheet first, then you can make one small change to the macro so that it uses a range for the print area: Printing Selected Rows Or Columns On Every Page of an Excel Worksheet Last month I talked about how you can freeze rows or columns in an Excel workbook so that your headings stay visible on screen. Make sure the destination printer is properly selected at the top of the dialog box. Follow these steps: Set your two ranges as your print area, as you have already done. SPrintArea = įor Each wks In ActiveWindow.SelectedSheets First, you could print multiple pages per sheet of paper. (When more than one worksheet is selected, the active worksheet is the one that is visible when you run the macro.) Consider the following macro, which will set the print area for all the selected worksheets to whatever the print area is on the active worksheet. The other option is to create a macro that will do the print-area setting for you. The copied worksheets will have the print area set as it was in the first worksheet. That leaves two ways to set the Print Area in VBA, by accessing the PageSetup object and by accessing the Names collection object. It doesn’t appear to be any more complicated than that because you can create that Name manually and get the same effect. Then, copy the worksheet however many times desired in the workbook. When you set the Print Area (File>PageSetup>Sheet Tab), Excel creates a Name called PrintArea. Use page breaks Here, you can set the print area and print titles. This menu contains the options to print header row in Excel to repeat on every printed page. Excel automatically provides headings for columns (A, B, C) and rows (1, 2, 3). One is to start with a new workbook and develop a single worksheet that contains the print area as you would want it on all worksheets. Excel displays the Page Setup dialog box. There are several things you can try, however. When you select multiple worksheets, select the area you want set as the print area, and then try to set the print area, you quickly discover that the option to do the setting is grayed out, so you cannot select that option.
He has a workbook containing a number of worksheets structured exactly the same, and he wants their respective print ranges to be exactly the same.Īs Martin has discovered, there is no way to do this directly in Excel. It also creates a named range for the selected area (the name Print_Area would be visible in the Name Box).Martin asked if there is a way to set print ranges for multiple worksheets at the same time. The system is set to print the active sheets by default which means it will print the entire worksheet. Click Ctrl + P and then select Print Selection in the Print settings. While holding down the Ctrl key, click on each of the other individual sheets you want to print. This would set the selected cells as the print area. Highlight or select the range of cells you want to print. Go to Page Layout –> Page Setup –> Print Area –> Set Print Area. Select the range of cells that you want to set as the print area in that Excel worksheet. Excel marks each of your ranges as part of the print area. In the Page Setup group click the Print Area tool and then click Set Print Area. Hold down the Ctrl key and use the Name Box drop-down list to select each of the other named ranges you want to print. To print only the selected area, in Print Options, click Current Selection. On the worksheet, click and drag to select the cells you want to print. How can you print selected cells of your worksheet? On the Page Layout tab, in the Page Setup group, click Print Area > Set Print Area.This problem is stated as printing non contiguous rows. For example if you need to print only partial data on a sheet which is not at one place. Scenario: Working with Printing Excel sheets, Excel does not allow you to print multiple ranges or columns of a sheet on page.
Select the part of the worksheet that you want to print. In this article, we will learn How to Print Non-Contiguous Areas in Excel.