Copy print areas

Topic

New Reply

Excel 2010: How can we copy all the print areas from one workbook to another?

Reply | Quote

Replies

Bonriki,

This code will transfer the print area settings form the active workbook with a sheet named Source to a second workbook called Book2 that has a worksheet called Target. The second code cycles through each sheet in the workbook and transfers its print area. These codes assumes both workbooks are open and are run from the source workbook being the active book.

HTH,
Maud

Transfer print area from single sheet:

Code:

Public Sub TransferPrintArea()
Dim wb1 As Workbook
    Set wb1 = Workbooks(“Book2.xlsm”)
    With wb1.Worksheets(“Target”)
    .PageSetup.PrintArea = Worksheets(“Source”).PageSetup.PrintArea
    End With
End Sub

Transfer print area from each sheet:

Code:

Public Sub TransferPrintArea()
Dim wb1 As Workbook
Set wb1 = Workbooks(“Book2.xlsm”)
For I = 1 To Worksheets.Count
wb1.Worksheets(I).PageSetup.PrintArea = Worksheets(I).PageSetup.PrintArea
Next I
End Sub

Reply | Quote

Maud hi
We have a wb with 28 sheets and 64 print areas defined. The data in the source and target files is essentially the same.
Using the second code it appears to run OK but nothing happens

Reply | Quote

These codes assumes both workbooks are open and are run from the source workbook being the active book.

Bonriki,

Make sure you are looking at the source book (the file that has the print areas defined) and not the target book. Also, Make sure you are pasting the code into the source book as well.

If you are looking at the target book, it will essentially copy blank print areas and paste them on to itself. It will appear as nothing happened and that is exactly right.

Reply | Quote

Maud
I still cannnot get this to work.
Tried renaming and saving target file as .xslx. copying all the macros across ( The macros are the print commands that use the print areas), bot to no avail!

Reply | Quote

Bonriki,

Attached are the 2 workbooks. Run the code from Source.xlsm. Both books must be open in the same Excel window not different ones. I have place a button to run the code in the Source.xlsm Sheet1 to make sure the right workbook is active. When the code runs, each sheet’s print areas (3 on each) in the source workbook is copied to the corresponding sheet by index in the target workbook assuming they hace the same number of sheets..

Code:

Public Sub TransferPrintArea()
Dim wb1 As Workbook
Set wb1 = Workbooks(“Target.xlsx”)
For I = 1 To Worksheets.Count
wb1.Worksheets(I).PageSetup.PrintArea = Worksheets(I).PageSetup.PrintArea
Next I
End Sub

Reply | Quote

Maud hi
I think I have got to the bottom of my problem.
I asked if I could copy print areas when I shoulkd have asked “Can I copy named ranges” instead.

I have attached a screen dump of a range named “Consolidated” and part of the highlighted area.
35784-printares

Maud
I should have said the print area copy example worked fine

Reply | Quote

Bonriki,

If you want to copy named ranges then use the following code:

Code:

Public Sub CopyRanges()
For Each x In ActiveWorkbook.Names
    Workbooks(“Target1.xlsx”).Names.Add Name:=x.Name, RefersTo:=x.Value
Next x
End Sub

Note that all the ranges will appear in the target Name Manager, however, they will only apply to the sheets if the sheet name is the same

Reply | Quote

Thanks Maud
Finally got it to run Kept failling with the code asking for the target file to be opened again
Inserted error code “On error resume next” and works fine

Thank you again

Reply | Quote

Strange, but OK as long as you are happy.

Maud

Reply | Quote