Programming a pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 19 Sep 2007 19:34 GMT

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?

Reply to this Message

Tom Ogilvy - 19 Sep 2007 19:50 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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?

Reply to this Message

Jim - 19 Sep 2007 20:56 GMT

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.

> 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.
>
> > 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?

Reply to this Message

Dick Kusleika - 19 Sep 2007 23:24 GMT

>Thanks Tom,
>
[quoted text clipped - 5 lines]
>> > 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?

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

Signature

Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Reply to this Message

Jim - 20 Sep 2007 14:52 GMT

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!!

> >Thanks Tom,
> >
[quoted text clipped - 28 lines]
> TableName:= _
> "PivotTable1", DefaultVersion:=xlPivotTableVersion10

Reply to this Message

Dick Kusleika - 20 Sep 2007 18:13 GMT

>Thank you Dick,
>
[quoted text clipped - 9 lines]
>
>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.

Signature

Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Reply to this Message