Results 1 to 12 of 12
  1. #1
    rpray
    Guest

    printing (excel 2000)

    I would like to copy or transfer print settings from one sheet in a workbook to another workbook with the same sheet name. Or, I would like to copy the entire print settings from all the sheets in a workbook to another workbook with the same filename, but located in another folder. Can you help?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: printing (excel 2000)

    rpray

    OK let me get this straight, when you say: "print settings" do you mean Page Setup or actual printer settings?

    For Page Setup you can record a macro that would apply the same settings to all worksheets you run it on. For example this code makes the first row of the worksheet print on each page, and sets all the margins to zero.

    Sub SetPageSetUpSettings()
    '/This will make Row One print on the top of all pages.
    '/This is good when you have column titles.
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    End With

    '/This section will make all margins set to zero, so that
    '/you can get the most on a page.
    With ActiveSheet.PageSetup
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    End With
    End Sub


    Now if I run this macro code on any worksheet it will have zero margins and that would be true to any worksheet.

    I don't know why you want to have two workbooks with two sheets named the same, but again this is your business not mine, so I will try and help. This is an example of how to find what sheet goes with which based on names:

    Sub CompareSheetNamesAndSetPageSetUpSettings()
    Dim i As Integer '/Simple For-Next Counter.
    Dim j As Integer '/Simple For-Next Counter.

    For i = 1 To Workbooks("First Workbook.XLS").Worksheets.Count
    For j = 1 To Workbooks("Second Workbook.XLS").Worksheets(j).Name
    If Workbooks("First Workbook.XLS").Worksheets(i).Name = _
    Workbooks("Second Workbook.XLS").Worksheets(j).Name Then
    '/Found the worksheet so do your thing.
    With Workbooks("Second Workbook.XLS").Worksheets(j).PageSetup
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    End With
    '/End your search and get out!!!
    Exit Sub
    End If
    Next j
    Next i
    End Sub


    Hope these examples help...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    rpray
    Guest

    Re: printing (excel 2000)

    O.K. I do mean Page Setup. Your last macro gets me closer to what I am needing. I would like to identify the same sheet name in another file; then copy the Page Setup parameters (i.e., margins, headers, footers, %zoom, etc.) from one sheet / workbook to another workbook with the same sheet name. Bottom line is that I have numerous sheet(s) in multiple workbooks that I would like printed using the same Page Setup.

    Thanks for you help.

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: printing (excel 2000)

    rpray

    OK so where you see the For-Next loops take them out and replace them with a call to the worksheet you want to work with. One at a time since you have multiples and you should be able to take my sample and use it with some minor modifications.

    Try and come up with something and then post it here, and I will help you adjust it.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    rpray
    Guest

    Re: printing (excel 2000)

    O.K.; now how would I set a more specific margin, fit-to-page(s) wide vs. tall, and then format any of the soft page breaks. Maybe set up a dialogue for entering a header or footer. Could I have the macro only do a print on a specific range, similar to setting the print area?

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: printing (excel 2000)

    rpray

    OK for the Fit-To-Page use the following example:

    Sub FitToPage()
    With ActiveSheet.PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 2
    End With
    End Sub

    So you now want to adjust soft page breaks. No can do with a Fit-to-Pages ???. This is the way the system works.

    A dialog will be OK, but I would prefer an InputBox, less coding and better suited for the task at hand. Also you can dump the contents into a cell and spell check it for the user... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Yes for the print Range, but the Page Setup is part of the Worksheet not the range. I guess the Range will follow the Worksheet settings, plus a bit more.

    Do you have any code sample for all of that?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    rpray
    Guest

    Re: printing (excel 2000)

    No, I do not have any code sample for this, which would be great. A separate question for you; do you know of a way just to copy the Page Setup of one sheet to another sheet in another file?

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: printing (excel 2000)

    rpray

    You are supposed to write something up, and then we will all help you with it, but its not going to do you any good is you don't try it out for yourself.

    Plus is that not your original question? Instead of copying the settings, and I am not sure how without a macro, how about doing the following:

    Assume the sheet you want to copy the settings from is called SettingsSheet and the sheet you want to make the same is called TargetSheet:

    1) Copy SettingsSheet and name it TargetSheet.
    2) Empty the cells in TargetSheet
    3) Repopulate TargetSheet with the new info.

    TargetSheet and SettingsSheet have the same PageSetup settings. Now you see why you need a macro?! <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: printing (excel 2000)

    Hi,
    Unfortunately there is no neat way of doing this in code - you can't just say Sheets(1).pagesetup = Sheets(2).pagesetup. You can do it however by specifying every single page setup setting that you want to use:- e.g.
    Workbooks(1).worksheets(1).pagesetup.orientation = workbooks(2).worksheets(1).pagesetup.orientation
    and so on for each pagesetup property.
    I hope that's of some use.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: printing (excel 2000)

    Rory,

    I've found that setting up page setup settings individually can be VEEEERY slow, for some reason. That may be OK when you've only got a few pages, but when you've got hiundrededs, it's not practicable.

    What I ended up doing was to copy a sheet which had all the page settings I wanted, then deleted all contents on the sheet. Or even to do the setting up on a new blank sheet, and then copy that sheet again as many times as I needed.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: printing (excel 2000)

    Geoff,
    That's very true - particularly if you have pagebreaks displayed.
    It still bugs me though that the PageSetup object doesn't have a Properties collection that you can loop through to do something like this. Maybe that's intended to dissuade you from trying it?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: printing (excel 2000)

    Something that has consistently worked for me is:
    <UL><LI>Sheet 1 - Workbook 1 with desired PageSetup
    <LI>Target Workbook without desired PageSetup[/list]
    1. <LI>copy Sheet 1 to Target Workbook
      <LI>select all sheets in Target Workbook (right-click on tab name) while the inserted Sheet 1 has the focus
      <LI>select PageSetup (verifying that the desired defaults have carried over
      <LI>click OK in the PageSetup dialog
      <LI>deselect the Sheets Grouping (either by right-clicking on tab name or by flipping to another Sheet)
      <LI>delete the imported Sheet 1
    Haven't tried using VBA to copy a Sheet from one Workbook to another. Subject to this, a working macro should be writable.
    Gre

Posting Permissions

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