Excel macro (VB) - HELP! 1

[TheBlade]

I've got a spreadsheet with 10 pivot tables (each on separate sheets)...below is a macro for updating one pivottable and moving narrative underneath it pending on whether the table expands/contracts...how can i loop it so that the macro does it for each of the 10 pivottables and then stops (instead of having the macro below, but 10 times longer)...As you can tell, I don't much on VB, so ANY help would be very much appreciated. Thanks


Sub LeadsUpdate()

'Determine how much space the pivottable and leadsheet narrative needs
Columns("a:a&quot.Select
Selection.find(What:="audit objectives", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
ActiveCell.Select

'This is where the leadsheet narrative starts
Dim var As String
var = ActiveCell.Row

'This is where the leadsheet narrative should be
Dim var2 As String
var2 = Range("d5&quot
Dim var3 As String
var3 = Range("e5&quot

'Determine what should be moved first - pivot table or narrative
If var2 > var Then GoTo Option1 Else GoTo Option2:

Option1:
'Move narrative
ActiveCell.Rows("1:100&quot.EntireRow.Select
Selection.Cut
Range(var3).Select
ActiveSheet.Paste
ActiveCell.Select

'Set pivottable category and refresh pivottable data
Dim var4 As String
var4 = Range("c5&quot
ActiveSheet.PivotTables("PivotTable4&quot.PivotFields("Subject:&quot.CurrentPage = _
var4
ActiveSheet.PivotTables("PivotTable4&quot.RefreshTable
GoTo Finish:

Option2:
'Set pivottable category and refresh pivottable data
Dim var5 As String
var5 = Range("c5&quot
ActiveSheet.PivotTables("PivotTable4&quot.PivotFields("Subject:&quot.CurrentPage = _
var5
ActiveSheet.PivotTables("PivotTable4&quot.RefreshTable

'Move narrative
ActiveCell.Rows("1:100&quot.EntireRow.Select
Selection.Cut
Range(var3).Select
ActiveSheet.Paste
ActiveCell.Select


Finish:
Range("a1&quot.Select
End Sub

 

[ETID]

Add this to the module, and change the "pivotx sheet names" to your real sheet names.

Then run your proceedure through this sub.

Sub Loop_all_pivots()

For i = 1 To Worksheets.Count

Select Case Worksheets(i).Name
Case Is = "pivot1 sheet name"
x = 1
Case Is = "pivot2 sheet name"
x = 1
Case Is = "pivot3 sheet name"
x = 1
Case Is = "pivot4 sheet name"
x = 1
Case Is = "pivot5 sheet name"
x = 1
Case Is = "pivot6 sheet name"
x = 1
Case Is = "pivot7 sheet name"
x = 1
Case Is = "pivot8 sheet name"
x = 1
Case Is = "pivot9 sheet name"
x = 1
Case Is = "pivot10 sheet name"
x = 1
Case Else
x = 0
End Select

If x = 1 Then LeadsUpdate

Next
End Sub

 

[ETID]

Any one home?

 

[SkipVought]

Here's a solution that is more general:

Sub UpdatePivotTables()
    For Each Worksheet In Application.Worksheets
        For Each PivotTable In Worksheet.PivotTables
            'do the stuff for your PivotTable
        Next
    Next
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[ETID]

Nice code Skip....

I wasn't aware that the Worksheet.PivotTables propertie had a count.