Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Multiple Worksheets (Excel 97)

    Using the macro tool I determined I can select multiple worksheets by using:

    Sheets(Array("Carey", "Masters", "DeLong")).Select

    Now i have multiple exact changes I need to make to all three worksheets so I thought that I could just write one set of code and have it applied to all 3 sheets at the same time. So I have


    Sheets(Array("Billinger", "Masterson", "DeLuca")).Select

    'Delete Column B
    Columns("B").Delete shift:=xlLeft

    'Format Date Column
    Columns("R:R").NumberFormat = "mm/dd/yy"

    'Select Left 3 from concatenated column to get status

    Range([U2], [T2].End(xlDown).Offset(0, 1)).Formula = "=RIGHT(T2,3)"

    Columns("U:U").Copy
    Columns("U:U").PasteSpecial Paste:=xlValues

    'Moves Column U to Column B
    Columns("U:U").Cut
    Columns("B:B").Insert shift:=xlToRight

    Columns("U:U").Delete

    Columns("T:T").Cut
    Columns("J:J").Insert shift:=xlToRight

    This formatting will go on for awile more <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    When I run this it only makes changes to the DeLong Worksheet. Is there a way to have it apply it to all 3?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    Dim ws As Worksheet
    For Each ws In Sheets(Array("Sheet2", "Sheet4"))
    Range([U2], [T2].End(xlDown).Offset(0, 1)).Formula = "=RIGHT(T2,3)"
    ' <other actions>
    Next ws
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    John,

    That doesn't seem to be working for me. It makes the same changes to the first worksheet 3 times..

    Here is what I have:

    Dim ws As Worksheet
    For Each ws In Sheets(Array("Billinger", "Masterson", "DeLuca"))
    Range([U2], [T2].End(xlDown).Offset(0, 1)).Formula = "=RIGHT(T2,3)"

    Columns("U:U").Copy
    Columns("U:U").PasteSpecial Paste:=xlValues

    'Moves Column U to Column B
    Columns("U:U").Cut
    Columns("B:B").Insert shift:=xlToRight

    Columns("U:U").Delete

    Columns("T:T").Cut
    Columns("J:J").Insert shift:=xlToRight

    Next ws

    What code does it seem I have missed?

    Thank you for all your help..

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    It would appear I'm not so helpful. I think that the .End(xltosomedirection) method only works with an active sheet, so try:

    Dim ws as Worksheet
    Application.ScreenUpdating = False
    For Each ws In Worksheets(Array("Billinger", "Masterson", "DeLuca"))
    ws.Activate
    With ActiveSheet
    .Range([T2], [T2].End(xlDown)).Offset(0, 1).Formula = "=RIGHT(T2,3)"
    .Columns("U:U").Copy
    .Columns("U:U").PasteSpecial Paste:=xlValues
    .Columns("U:U").Cut
    .Columns("B:B").Insert shift:=xlToRight
    .Columns("U:U").Delete
    .Columns("T:T").Cut
    .Columns("J:J").Insert shift:=xlToRight
    End With
    Next ws
    Application.ScreenUpdating = True
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    That did the trick!

    Thank you so much my helpful friend [img]/forums/images/smilies/smile.gif[/img]

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    John,

    I'd suggest that there is no need to activate. I think that something like the following should work,

    For Each ws In Worksheets(Array("Billinger", "Masterson", "DeLuca"))
    With ws
    <font color=red>.</font color=red>Range(<font color=red>.</font color=red>[T2], <font color=red>.</font color=red>[T2].End(xlDown)).Offset(0, 1).Formula = "=RIGHT(T2,3)"
    .Columns("U:U").Copy

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    Thanks, Andrew. I could not see for the life of me why it wouldn't run without activating each worksheet, but now I see that each and -every- Range ref had to be qualified to the With ... exactly as you say! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    Hi Andrew,

    I tried your code, but it didn't seem to work for me. It doesn't cycle through the worksheets, it keeps updating the same worksheet over and over.

    -Leslie

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    John/Andrew....

    Maybe you could help me with another question.... John - I have used your solution of cycling through the worksheets. Here is the code I have currently:

    Sub CreateSDFDetailSheets()

    Application.StatusBar = "Creating SDF Detail"

    'Cycles through the Billinger, Master & Deluca worksheets to apply formatting & changes
    Sheets("Billinger").Select
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("Billinger", "Masterson", "DeLuca"))
    ws.Activate
    With ActiveSheet
    'Get's the Risk
    .Range([T2], [T2].End(xlDown)).Offset(0, 1).Formula = "=RIGHT(T2,3)"

    'Column Alignment
    .Columns("U:U").Copy
    .Columns("U:U").PasteSpecial Paste:=xlValues
    .Columns("U:U").Cut
    .Columns("B:B").Insert shift:=xlToRight
    .Columns("U:U").Delete
    .Columns("T:T").Cut
    .Columns("J:J").Insert shift:=xlToRight
    .Columns("D").Cut
    .Columns("c:c").Insert shift:=xlToRight
    .Columns("t:T").Cut
    .Columns("F:F").Insert shift:=xlToRight

    'Sort by FBC & Risk
    Range("B1").Value = "Risk"
    Range("A1").Select
    Selection.sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

    'Subtotals
    Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True

    'Adding SDF Name & Period to Cell A1
    Range("A1").Value = "Period " & Period & " Forecast"

    'Formatting
    Cells.Font.Name = "Arial"
    Cells.Font.Size = 10
    Rows("1:1").Font.Bold = True
    Rows("1:1").WrapText = True
    Rows("1:1").HorizontalAlignment = xlCenter
    Cells.EntireColumn.autofit

    'Adding SDF Name & Period to Cell A1
    Range("A1").Value = "Period " & Period & " Forecast"

    'Freeze Panes
    Range("F2").Select
    ActiveWindow.FreezePanes = True
    End With
    Next ws

    End Sub


    This is called from another sub that has the Application.ScreenUpdating = False in it.

    Now when I run this code it takes two minutes to complete. (I timed it <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

    Each of these worksheets only has approximately 60 lines in it. Can you tell me if I have any extraneous code that is causing the slow-down (Also, the screen turns white during this process)

    -Leslie

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    Are you sure you got those dots (periods) in front of each square bracket? See attachment.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    Several thoughts:

    1. If we can get Andrew's version to work, not activating each worksheet will run the code faster.
    2. Since eventually you want to get the lefmost three characters, and past them to col B, you don't need to run the formulas and then past the values; you can paste straight into Col B as values using VBA
    3. The column movements seem a bit stange; could they be more efficient?
    4. Turn screenupdating off in the beginning, back on at the end
    5. If there are a lot of formulas, turn recalc off at the beginning and turn back on at the end.

    Can you post a censored sample so it's easier to see the whole picture of what you are doing?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    Yup, I sure did.

    Also - I had to add Sheets("Billinger").select before the For Each or it just runs the code on the worksheet it is on.

    I downloaded your sheet and copied and pasted right into my code. Here is what it did

    It did the first two sets of items: The concatenation & cell cutting but when it got to the subtotaling, it just kept repeating it on the Billinger sheet. Is this because I don't have addition periods in the other parts of my code?

  13. #13
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    John,

    I was posting at the same time as you were.

    1. I think my problem is the sorting/subtotaling
    2. So I should add column B first and then put the left characters there
    3. Yeah, they do seem strange. I'm putting the spreadsheet into a more readable format, I'll relook at this part to make sure it is effiecient.
    4. Screenupdating is turned off in the sub that runs this one and then turned back on at the end
    5. The only forumla on this page is the left 3 characters

    I posted what i have so far in a post above. I wish I could post everything including the worksheet I start off with, but it would take me forever to alter it enough for public view! [img]/forums/images/smilies/smile.gif[/img]

    Just to give you an idea of what I am doing - We need a report formatted in a certain way. I download an excel file from one of our databases and then have to do A LOT of additional work to make it what the users need. That is why I am doing it in VBA. This is something that get's done 4 times a month and it will save a lot of time to automate it.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Multiple Worksheets (Excel 97)

    Yes, some of the code is incorrectly hitting Billinger because it needs periods in front of the actions to place those ranges "With" the ws currently specificified by "With ws".
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Multiple Worksheets (Excel 97)

    John/Andrew..

    Thank you both so much for your help.

    I will create a modified spreasheet to post so I can show you a better example of what I am doing...

    Thanks again for your helpl...!

Page 1 of 2 12 LastLast

Posting Permissions

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