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