Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    Hi XL Loungers:
    I need some help again. I have 5 sheets named ANusers, PnUsers, CCUsers, HNUsers, and NHusers each with A named ranges A_Print_AN, A_Print_PN, A_Print_CC, A_Print_HN, and A_Print_NH, respectively. I have written the code, shown below, to copy one of these ranges to another WS and print it from that WS. The reason for doing this is so the table can be sorted by column C before printing. I can not do this on the original WS because the data are derived from a Pivot Table that must be sorted a different way.
    I would like to repeat this process for each of the 5 WS and each time pick up a header for the printout that is located in cell "E1" of each of the XXUsers WSs. Each of the "A_Print_XX" tables have identical headers but they vary as to the number of rows, so I guess I will have to define dynamic ranges for each one like the range "A_Print_AN" is in the code below (which copies and prints the data from the "ANUsers" WS).

    Sub PrintRank_Order()
    Worksheets("RankOrder").Activate
    Worksheets("RankOrder").Range("A1100").ClearContents
    Worksheets("ANUsers").Activate
    ActiveSheet.Range("A_Print_AN").Select
    Selection.Copy
    Worksheets("RankOrder").Activate
    ActiveSheet.Range("A1:A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveSheet.Range("PrintOrderRank").Sort _
    Key1:=Worksheets("RankOrder").Range("C1"), _
    Header:=xlYes
    Range("prcnt_stdRevRate_data").Select
    Selection.NumberFormat = "0.00"
    Range("prcnt_ineffy_data").Select
    Selection.NumberFormat = "0.00"
    ActiveSheet.Range("PrintOrderRank").Select
    Worksheets("RankOrder").PageSetup.PrintArea = Range("PrintOrderRank").Address
    Selection.PrintOut Copies:=1, Collate:=True
    End Sub

    Any help in writting this code (or a more efficient version of it) to sort and print the data from the 5 WS will be appreciated.
    Thanks <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    Try something like this:

    <pre>Sub PrintRank_Order()
    Dim oWS As Worksheet
    Dim strABR As String
    For Each oWS In ActiveWorkbook.Worksheets
    If oWS.Name <> "RankOrder" Then
    Worksheets("RankOrder").Range("A1100").ClearContents
    strABR = Left(oWS.Name, 2)
    oWS.Range("A_Print_" & strABR).Copy
    Worksheets("RankOrder").Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Worksheets("RankOrder").Range("PrintOrderRank").So rt _
    Key1:=Worksheets("RankOrder").Range("C1"), _
    Header:=xlYes
    Worksheets("RankOrder").Range("prcnt_stdRevRate_da ta").NumberFormat = "0.00"
    Worksheets("RankOrder").Range("prcnt_ineffy_data") .NumberFormat = "0.00"
    Worksheets("RankOrder").PageSetup.PrintArea = Range("PrintOrderRank").Address
    Worksheets("RankOrder").PrintOut Copies:=1, Collate:=True
    End If
    Next oWS
    End Sub
    </pre>


    I also removed all of the activating and selecting that you were doing which will make this run much faster.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    Legare:
    Thanks for the code. It seems like you assumed that the only WS in the WB are "RankOrder" and the 5 named ANUsers, HnUsers, PNUsers, CCUsers, and NHUsers that I mentioned in my Post. There are others. So I changed the line:
    If oWS.Name <> "RankOrder" Then
    to
    If right(oWS.Name,5) = "Users" Then
    It printed the leftmost WS, which happened to be HNUsers and stopped there. The code I am using is as follows:
    Sub PrintRank_Order()
    Dim oWS As Worksheet
    Dim strABR As String
    For Each oWS In ActiveWorkbook.Worksheets
    If Right(oWS.Name, 5) = "Users" Then
    Worksheets("RankOrder").Activate
    Worksheets("RankOrder").Range("A1100").ClearContents
    strABR = Left(oWS.Name, 2)
    oWS.Range("A_Print_" & strABR).Copy
    Worksheets("RankOrder").Range("A1").PasteSpecial Paste:=xlValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Worksheets("RankOrder").Range("PrintOrderRank").So rt _
    Key1:=Worksheets("RankOrder").Range("C1"), Order1:=xlAscending, Header:=xlYes
    Worksheets("RankOrder").Range("prcnt_stdRevRate_da ta").NumberFormat = "0.00"
    Worksheets("RankOrder").Range("prcnt_ineffy_data") .NumberFormat = "0.00"
    Worksheets("RankOrder").Range("Prcnt_of_Std").Numb erFormat = "0.0%"
    Worksheets("RankOrder").PageSetup.PrintArea = Range("PrintOrderRank").Address
    Worksheets("RankOrder").PrintOut Copies:=1, Collate:=True
    End If
    Next oWS
    End Sub

    ALSO, I NEED TO PICK UP EACH WS NAME (ANUSERS, PNUSERS, ETC) IN THE HEADER OF THE TABLE THAT IS PRINTED.

    Thanks
    Stephen

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    To put the name in the header, use something like this:

    <pre> Worksheets("RankOrder").PageSetup.CenterHeader = oWS.Name
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    Legare:
    I forgot to mention that the code I sent you does not cycle through the WSs. It prints the first one and stops.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    The only thing that I can see that might cause that is that the right five characters of the other sheets is not exactly "Users". For example, it might be "USERS" or "Users " (with a space after the last s). you might try changing your If statement to:

    <pre> If UCase(Right(Trim(oWS.Name), 5)) = "USERS" Then
    </pre>


    If that does not fix it, then could you attach a copy of the sheet to a message so I can look at it?
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle thru 5 WS with a copy, paste, print macro. (EXCEL 97 SR2)

    I did try this today and found that the UC fixed it. Some of my sheetnames were Upper and lower and others were all upper.
    Thanks

Posting Permissions

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