Macro for Multiple Worksheets (Excel 2002)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

Reply | Quote

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

Reply | Quote

[indent]

---

If InStr(ws.Name, “Budget”) > 0 Then

---

[/indent]

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

Reply | Quote

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.

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

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.

Reply | Quote