Pivot macro problem

[Boss]

I have a macro to make pivot...

Sub MakeTable()
Dim Pt As PivotTable
Dim strField As String

'Pass heading to a String variable
strField = Selection.Cells(1, 1).Text

'Name the list range
Range(Selection, Selection.End(xlDown)).Name = "Items"

'Create the Pivot Table based off our named list range.
'TableDestination:="" will force it onto a new sheet
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="=Items").CreatePivotTable TableDestination:="", _
TableName:="ItemList"

'Set a Pivot Table variable to our new Pivot Table
Set Pt = ActiveSheet.PivotTables("ItemList")

'Place the Pivot Table to Start from A3 on the new sheet
ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)

'Move the list heading to the Row Field
Pt.AddFields RowFields:=strField
'Move the list heading to the Data Field
Pt.PivotFields(strField).Orientation = xlDataField


End Sub

The problem is its creating new sheet every time. i tried recording and
keeping a new worksheet location code but failed.

Please help me.. so that i can get same worksheet and same table name every
time i run macro. Its important because i have to run vlookup etc.

Thanks
Boss

 

[Debra Dalgleish]

You could name the new sheet after it's created.

Dim wsPT As Worksheet

Set wsPT = ActiveSheet
wsPT.Name = "PivotSheet"
Set pt = wsPT.PivotTables("ItemList")

 

[Boss]

This was really out of the box..
Thanks!!
Boss

You could name the new sheet after it's created.

Dim wsPT As Worksheet

Set wsPT = ActiveSheet
wsPT.Name = "PivotSheet"
Set pt = wsPT.PivotTables("ItemList")