Pivot macro problem

B

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
 
D

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")
 
B

Boss

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

Debra Dalgleish said:
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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top