×
×לי
Hi!
I am trying to create a general macro that will create pivot table. my
problem is how to create the "SourceData:=" and "TableDestination:="
locations in such a way that it will fit the name of the current sheet?
note: the location of the table in the sheet will be always "L1".
I tried:
Dim ws, wb As String
Dim LastRow As Long
ws = ActiveSheet.Name
wb = ThisWorkbook.Name
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"ws!R1C1:R" & LastRow & "C10").CreatePivotTable TableDestination:= _
"'[wb]ws'!R1C12", TableName:="PivotTable1",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cycle_Num", _
ColumnFields:="Step_Num"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Capacity fix")
.Orientation = xlDataField
.Caption = "Capacity fix"
.Function = xlMax
End With
But it is not working....
Thanks in advance for your help.
Eli
I am trying to create a general macro that will create pivot table. my
problem is how to create the "SourceData:=" and "TableDestination:="
locations in such a way that it will fit the name of the current sheet?
note: the location of the table in the sheet will be always "L1".
I tried:
Dim ws, wb As String
Dim LastRow As Long
ws = ActiveSheet.Name
wb = ThisWorkbook.Name
LastRow = Range("A" & Rows.Count).End(xlUp).Row
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"ws!R1C1:R" & LastRow & "C10").CreatePivotTable TableDestination:= _
"'[wb]ws'!R1C12", TableName:="PivotTable1",
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cycle_Num", _
ColumnFields:="Step_Num"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Capacity fix")
.Orientation = xlDataField
.Caption = "Capacity fix"
.Function = xlMax
End With
But it is not working....
Thanks in advance for your help.
Eli