Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub Macro4FixedToLoop()
Dim mySht As Worksheet
Dim myR As Range
Dim myC As Range
Dim i As Integer

Set myC = Worksheets("SheetRef").Range("K1")
i = 1

For Each mySht In Worksheets
If mySht.Name < "SheetRef" Then
Set myR = mySht.Range("A8:B8")
Set myR = mySht.Range(myR, myR.End(xlDown))
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=myR).CreatePivotTable TableDestination:=myC, _
TableName:="PivotTable" & i
Worksheets("SheetRef").PivotTables("PivotTable" & i).SmallGrid = False
With Worksheets("SheetRef").PivotTables("PivotTable" & i) _
.PivotFields("Agent Number/Name")
.Orientation = xlRowField
.Position = 1
End With
With Worksheets("SheetRef").PivotTables("PivotTable" & i) _
.PivotFields("Campaign Type")
.Orientation = xlDataField
.Position = 1
End With
i = i + 1
Set myC = myC.Offset(0, 3)
End If
Next mySht
End Sub



"Hru48" wrote in message
...

Right so this macro works for the first pivot:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/6/2006 by hru48
'

Range("A8:B8").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'01'!R8C1:R477C2").CreatePivotTable
TableDestination:=Worksheets("SheetRef").Range("k1 "), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Agent
Number/Name")
Orientation = xlRowField
Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Campaign
Type")
Orientation = xlDataField
Position = 1
End With
Windows("New_jersey_agent.xls").Activate
Range("B11").Select
End Sub


Does anyone know how I would approach getting it to work for another
one with the same properties?

I'm aiming to get this macro to repeat for all number worksheets (above
was 01) until it gets to 'SheetRef' where it should stop. The pivot
should all be place on sheetRef as well one column apart from each
other. Is this possible?

I'm having trouble as I can't figure out how to change the source data
or the table name - any ideas?

Cheers


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=558821