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:=xlDatab ase, 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

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:=xlDatab ase, 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

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:=xlDatab ase, 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 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:=xlDatab ase, _
SourceData:=mySource.address(external:=true)).Crea tePivotTable _
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.


??? wrote:

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:=xlDatab ase, 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

--

Dave Peterson