Pivot tables and loop
 

Hi,

i'd like to put a pivot table macro in a loop.

what i get: 30 worksheet that contains data.

each worksheet (and its name) it's automatically generated by a loop
like this:

Sub mib30()

Dim symbol As String
Dim sdate As String
Dim sheet_name As String
Dim starget_range As String

sdate = Application.WorksheetFunction.Substitute(CStr(Date ), "-", "_")

For i = 1 To 30


symbol = Sheets("Summary").Cells(i, 1).Value
sheet_name = symbol + "_" + sdate
starget_range = sheet_name + "!" + "A1"

With Worksheets.Add
.Name = sheet_name
End With

Call get_data(symbol, starget_range)

Next i

End Sub

and generate names like: AL_17_10_2003, where 17_10_2003 is the
current date.

what i'd like to do is: automatically process this data with a Pivot
Table (one Pivot Table for each worksheet).

this the code of The pivot table for 1 worksheet:


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"AUTO_13_10_03!R1C1:R15000C8").CreatePivotTabl e TableDestination:="",
TableName:= _
"PivotTable10"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Volume
Ultimo")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Time Bin")
.Orientation = xlRowField
.Position = 1
End With

for me it's too tricky...

many thank's



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Pivot tables and loop
 

I have an addin which allows you to work with data on several sheets.
Instead of a multisheet pivot, you'll have all the benefits of a normal
pivot, while your data can stay in several sheets.

Will work if data layout on sheets is identical and total rows < 65000.

I've added an extra command ("3Drefresh"), which copies the data in
related named ranges to 1 worksheet, then swaps out the pivotcache.
BUT maintains layout.

if you're interested... MultiRangePivot on
http://members.chello.nl/keepitcool/download.html



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


ferrdav wrote:

Hi,

i'd like to put a pivot table macro in a loop.

what i get: 30 worksheet that contains data.

each worksheet (and its name) it's automatically generated by a loop
like this:

Sub mib30()

Dim symbol As String
Dim sdate As String
Dim sheet_name As String
Dim starget_range As String

sdate = Application.WorksheetFunction.Substitute(CStr(Date ), "-", "_")

For i = 1 To 30


symbol = Sheets("Summary").Cells(i, 1).Value
sheet_name = symbol + "_" + sdate
starget_range = sheet_name + "!" + "A1"

With Worksheets.Add
Name = sheet_name
End With

Call get_data(symbol, starget_range)

Next i

End Sub

and generate names like: AL_17_10_2003, where 17_10_2003 is the
current date.

what i'd like to do is: automatically process this data with a Pivot
Table (one Pivot Table for each worksheet).

this the code of The pivot table for 1 worksheet:


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"AUTO_13_10_03!R1C1:R15000C8").CreatePivotTabl e TableDestination:="",
TableName:= _
"PivotTable10"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").SmallGrid = False
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Volume
Ultimo")
Orientation = xlDataField
Position = 1
End With
With ActiveSheet.PivotTables("PivotTable10").PivotField s("Time Bin")
Orientation = xlRowField
Position = 1
End With

for me it's too tricky...

many thank's



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/