Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
How to create copy & paste loop--rows to new sheets | Excel Programming | |||
How to loop through PivotTable | Excel Programming | |||
Excel prints some sheets very small | Excel Discussion (Misc queries) |