Programming a pivot table

[Guest]

I'd like to put a pivot table on a seperate sheet from the data in MS-Excel
2003. What would the code look like?

 

[Guest]

Turn on the macro recorder (tools=>Macro=>Record a new macro) and perform the
action manually. then turn of the macro recorder and look at the recorded
code. That should get you well on your way.

 

[Guest]

Thanks Tom,

I did that originally and I walked through debugging my steps and found the
pivot table getting created in the same sheet as my data. I selected "new
sheet" when I went through the macro creator. I just can't figure out now
what to change to have it insert a new sheet, hence my questioin.

 

[Dick Kusleika]

Thanks Tom,

I did that originally and I walked through debugging my steps and found the
pivot table getting created in the same sheet as my data. I selected "new
sheet" when I went through the macro creator. I just can't figure out now
what to change to have it insert a new sheet, hence my questioin.

I get lines like this from the macro recorder:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R3703C62").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

It seems that setting the TableDestination argument (of the CreatePivotTable
method of the PivotCache object) to an empty string causes it to create a
new sheet.

However, I would create the sheet myself if I was doing it in code

Dim sh As Worksheet

Set sh = ActiveWorkbook.Sheets.Add

wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"MyRangeName").CreatePivotTable TableDestination:=sh.Cells(3, 1),
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10

 

[Guest]

Thank you Dick,

That was what I was looking for. While I was working on my issue I found
some other helpful information. I found that since the columns never change
in my data, only the qty of rows of info I decided to create a named range
for the pivot table data sheet in excel, export the data from MS-Access which
basically overwrites my old worksheet of information and then I just have to
do a quick refresh of the pivot table. I'm still testing my process to make
sure when there are fewer lines of data Access is overwriting the entire
sheet of data for my pivot data but this looks like another way to accomplish
what I want.

Thanks again!!

 

[Dick Kusleika]

Thank you Dick,

That was what I was looking for. While I was working on my issue I found
some other helpful information. I found that since the columns never change
in my data, only the qty of rows of info I decided to create a named range
for the pivot table data sheet in excel, export the data from MS-Access which
basically overwrites my old worksheet of information and then I just have to
do a quick refresh of the pivot table. I'm still testing my process to make
sure when there are fewer lines of data Access is overwriting the entire
sheet of data for my pivot data but this looks like another way to accomplish
what I want.

Thanks again!!

I agree. If you can avoid recreating the pivot table every time, that's
better. If you've never looked at Data > Import External Data > New
Database Query, you should check it out. That may eliminate the need to
import from Access, as you could just refresh the external data table. Also
note that you can create a pivot table based on external data. I think
that's the first choice on the pivot table wizard screen.

Good luck.