Our Service

Copying Print Areas when Copying Worksheets in Excel

Excel is a powerful tool that allows users to organize, analyze, and manipulate data in a variety of ways. One of the most useful features of Excel is the ability to copy worksheets, which can save users a lot of time and effort. However, when copying worksheets that contain print areas, users may encounter some issues. In this article, we will explore how to copy print areas when copying worksheets in Excel.

Understanding Print Areas in Excel

Before we dive into the specifics of copying print areas, let’s first review what print areas are in Excel. A print area is a specific range of cells that a user has designated to be printed when printing a worksheet. This can be useful when a user only wants to print a specific section of a worksheet, rather than the entire worksheet.

To set a print area in Excel, simply select the range of cells that you want to print, then go to the Page Layout tab and click on the Print Area button. From there, select Set Print Area. You can also clear a print area by selecting Clear Print Area from the same menu.

Copying Worksheets in Excel

Now that we understand what print areas are, let’s move on to copying worksheets in Excel. To copy a worksheet, simply right-click on the worksheet tab and select Move or Copy. From there, select the worksheet that you want to copy and choose where you want to copy it to. You can also choose to create a copy of the worksheet in a new workbook.

However, when copying worksheets that contain print areas, users may encounter some issues. By default, Excel does not copy print areas when copying worksheets. This means that if you have set a print area on the original worksheet, it will not be copied to the new worksheet.

Copying Print Areas in Excel

So, how can you copy print areas when copying worksheets in Excel? There are a few different methods that you can use.

Method 1: Copy and Paste

The first method is to simply copy and paste the print area from the original worksheet to the new worksheet. To do this, select the print area on the original worksheet, then right-click and select Copy. Next, go to the new worksheet and select the cell where you want to paste the print area. Right-click and select Paste Special. From there, select Formats and click OK. This will copy the print area formatting to the new worksheet.

Method 2: Use a Macro

If you need to copy print areas on a regular basis, you may want to consider using a macro. A macro is a set of instructions that can automate repetitive tasks in Excel. To create a macro that copies print areas, follow these steps:

  1. Open the Visual Basic Editor by pressing Alt + F11.
  2. Select Insert > Module.
  3. Paste the following code into the module:

Sub CopyPrintArea()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

If ws.PageSetup.PrintArea <> “” Then


ws.Range(“A1”).PasteSpecial xlPasteFormats

End If

Next ws

End Sub

  1. Save the macro and close the Visual Basic Editor.
  2. Go back to your Excel worksheet and run the macro by selecting Developer > Macros and selecting the CopyPrintArea macro.

This macro will loop through all of the worksheets in your workbook and copy any print areas that it finds. It will then paste the print area formatting to cell A1 on each worksheet.


Copying worksheets in Excel can be a great time-saver, but it can also be frustrating when print areas are not copied along with the worksheet. By using the methods outlined in this article, you can easily copy print areas when copying worksheets in Excel. Whether you choose to copy and paste or use a macro, these methods will help you streamline your workflow and get more done in less time.


Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.