Loop through sheets and create a small PivotTable on each
 

Sub CreatePivots()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Sheet1") Then
sh.Select False
Else
sh.Select
Columns("A:J").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
ActiveSheet.Range("K1").CurrentRegion).CreatePivot Table _
TableDestination:=ActiveSheet.Range("A1"), TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("DESCRIPTION")
..Orientation = xlRowField
..Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TRAN-AMOUNT"), "Sum of TRAN-AMOUNT", xlSum
End If
Next sh
End Sub


The code fails on this line:
sh.Select

Of course I tried this:
sh.Select True

Still fails. If I F8-through I can see that True = True, so I can't figure
out why the code doesn't run. Does anyone see something that I'm not seeing?

TIA,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Loop through sheets and create a small PivotTable on each
 

sh.Activate ?

Tim


"ryguy7272" wrote in message
...
Sub CreatePivots()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Sheet1") Then
sh.Select False
Else
sh.Select
Columns("A:J").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
ActiveSheet.Range("K1").CurrentRegion).CreatePivot Table _
TableDestination:=ActiveSheet.Range("A1"), TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("DESCRIPTION")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TRAN-AMOUNT"), "Sum of TRAN-AMOUNT", xlSum
End If
Next sh
End Sub


The code fails on this line:
sh.Select

Of course I tried this:
sh.Select True

Still fails. If I F8-through I can see that True = True, so I can't
figure
out why the code doesn't run. Does anyone see something that I'm not
seeing?

TIA,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Loop through sheets and create a small PivotTable on each
 

Finally got it:

Sub CreatePivots()
For Each sh In Worksheets
If (sh.Name) < "Sheet1" Then
sh.Select
Columns("A:J").Select
Selection.Insert Shift:=xlToRight

'etc., etc., etc.

End If
Next sh
End Sub

Hope this helps others...

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Sub CreatePivots()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Sheet1") Then
sh.Select False
Else
sh.Select
Columns("A:J").Select
Selection.Insert Shift:=xlToRight
Range("K1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
ActiveSheet.Range("K1").CurrentRegion).CreatePivot Table _
TableDestination:=ActiveSheet.Range("A1"), TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("DESCRIPTION")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("TRAN-AMOUNT"), "Sum of TRAN-AMOUNT", xlSum
End If
Next sh
End Sub


The code fails on this line:
sh.Select

Of course I tried this:
sh.Select True

Still fails. If I F8-through I can see that True = True, so I can't figure
out why the code doesn't run. Does anyone see something that I'm not seeing?

TIA,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.