Pivot table

[×לי]

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

 

[Daniel.C]

Hi.
You should put the variables out of the quotes :

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:= _
ws & "!R1C12", TableName:="PivotTable1"
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

HTH
Daniel

 

[Dave Peterson]

I would let excel do the heavy lifting.


Dim mySource as range
dim DestCell as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set mysource = .range("a1",.cells(lastrow,10))
end with

with thisworkbook.worksheets(activesheet.name)
set destcell = .cells(1,12) 'or .Range("L1")
end with

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=mySource.address(external:=true)).CreatePivotTable _
TableDestination:=destcell.address(external:=true), _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

ps.

Dim ws, wb As String
actually declares ws as a variant and wb as a string.

Dim ws as string, wb as string
would declare both as string.

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