Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
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. -- Regards, Tom Ogilvy "Jim" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
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. "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
On Wed, 19 Sep 2007 12:56:02 -0700, Jim
wrote: 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. "Jim" wrote: 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:=xlDatab ase, 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 -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
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" wrote: On Wed, 19 Sep 2007 12:56:02 -0700, Jim wrote: 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. "Jim" wrote: 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:=xlDatab ase, 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 -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming a pivot table
On Thu, 20 Sep 2007 06:52:01 -0700, Jim
wrote: 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. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Programming (Selecting Pivot Items) | Excel Programming | |||
Pivot table programming | Excel Programming | |||
Pivot Table Programming | Excel Programming | |||
Pivot Table Programming | Excel Programming | |||
Pivot table Programming | Excel Programming |