Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro for Multiple Worksheets (Excel 2002)

    Hi,
    I've got the following macro that isn't quite working. I didn't want to have to actually name every single worksheet that had the word "budget" in the worksheet name, so I used the If, Then statement. The error I keep getting is that there is no "For" to go with the "Next". Any help would be awesome.
    Thanks!
    Lana

    Sub testpt()

    For Each Worksheet In Worksheets

    If Worksheet.Name = "*Budget" Then

    ActiveSheet.PivotTables("PivotTable5").PivotFields ("Period"). _
    CurrentPage = Sheet6.Range("AK8").Value

    Else: Range("A1").Select

    Next Worksheet

    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Multiple Worksheets (Excel 2002)

    The error is because you have no "End If" for your macro.

    The method you are using to check for the Budget in the name will not work unless your sheet is actually "*Budget". You can use the InStr() to check for the word Budget in your sheet name. One other thing to consider is to declare your variables, with one side note - worksheet would not be a recommended variable name since it is already a member of the worksheet collection.


    Here is your macro:
    <pre>Sub testpt()
    Dim wrkSheet As Worksheet
    For Each wrkSheet In Worksheets

    If InStr(wrkSheet.Name, "Budget") > 0 Then

    ActiveSheet.PivotTables("PivotTable5").PivotFields ("Period"). _
    CurrentPage = Sheet6.Range("AK8").Value

    Else: Range("A1").Select
    End If
    Next wrkSheet

    End Sub
    </pre>


  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro for Multiple Worksheets (Excel 2002)

    Hi,
    Thanks so much for the help... the macro partly does what I need it to do, except it keeps looping back to the same worksheet over and over again, then it stops on it's own, acting likes it's finished. I'm a little confused on how the following line works:
    If InStr(ws.Name, "Budget") > 0 Then
    Does it mean if the worksheet name has the word "Budget" in it? Also, I'm unsure what the zero means.
    Thanks!!
    Lana

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro for Multiple Worksheets (Excel 2002)

    <hr>If InStr(ws.Name, "Budget") > 0 Then<hr>

    InStr(String1, String2) searches String1 to find instances of String2, and returns the starting position within String1, so

    InStr("Winter", "inter") would return 2.

    If String2 is not found, it returns zero - so testing for a return value > 0 is just testing to see if the search string has been found anywhere in the target string

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for Multiple Worksheets (Excel 2002)

    The main problem with your macro is that it loops through the worksheets but doesn't do anything with them. Also, Worksheet is not a good name for a variable, since it is the name of an object.

    I have assumed that you have a pivot table PivotTable5 on each of the sheets with Budget in the name, and that you want to set the page field for each of these.
    <code>
    Sub testpt()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    If wsh.Name Like "*Budget" Then
    wsh.PivotTables("PivotTable5").PivotFields("Period "). _
    CurrentPage = Sheet6.Range("AK8").Value
    End If
    Next wsh
    End Sub
    </code>
    The line that starts with wsh.PivotTables("PivotTable5") refers to a pivot table on wsh, the worksheet in the loop, instead of to the active sheet.

Posting Permissions

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