Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Copy print areas

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

    Phil Carter

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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
    Last edited by Maudibe; 2013-12-11 at 22:39.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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
    cheers

    Phil Carter

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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!
    cheers

    Phil Carter

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-15 at 15:20.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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.
    printares.JPG

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

    Phil Carter

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    bonriki (2013-12-16)

  10. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    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
    cheers

    Phil Carter

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Strange, but OK as long as you are happy.

    Maud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •