Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Pivot Table
Hiyee all... I'm a newbie in programming with VBA. I had post this Q i
"Excel-New User" but cant get the answer from there... So, I throw m Qeustion here... Hope someone can answer for me.. Thanks.. When I create the pivot table, I record the macro. After complet building the pivot table, I stop my macro. Hence, the next time I wan to built my pivot table, I will just click on macro. When I recorded it with macro, I can built it with the same amount o data. The problem here is, when I expanded my data, I cant get th correct pivot table.. Below is the example... For eg: I got the below 5 data: SalesRep Region Month Sales Amy North Jan 33488 Amy North Feb 47008 Amy North Mar 32128 Bob North Jan 34736 Bob North Feb 92872 After I create the pivot table while recording the macro, it will sho me the below macro code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/19/2004 by leecy3 ' ' ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Sheet1!R1C1:R6C4").CreatePivotTable TableDestination:="", TableName: _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Region ") .Orientation = xlPageField .Position = 1 End With ActiveSheet.PivotTables("PivotTable5").AddDataFiel ActiveSheet.PivotTables( _ "PivotTable5").PivotFields("Sales"), "Sum of Sales", xlSum With ActiveSheet.PivotTables("PivotTable5").PivotFields ("SalesRep") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Month") .Orientation = xlColumnField .Position = 1 End With Sheets("Sheet1").Select End Sub After that, I can run the pivot table automatically by juz choosing th macro1. The problem here is, If I expand my data as below: SalesRep Region Month Sales Amy North Jan 33488 Amy North Feb 47008 Amy North Mar 32128 Bob North Jan 34736 Bob North Feb 92872 Bob North Mar 76128 Chuck South Jan 41536 Chuck South Feb 23192 Chuck South Mar 21736 Chuc South Jan 41536 Chuc South Feb 23192 Chuc South Mar 21736 and I click on macro1, it give me the same pivot table and both of th Chuck and Chuc data didnt include inside the pivot table. How can modify the code in macro1 so that I can execute the macro1 that recorded ? How to make it flexible and it wont fix on the size of th data? How to make the pivot table will expand follow by the expands o data? Hope to hear from whoever that know to solve this question... thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Pivot Table
How to make it flexible and it wont fix on the size of the
data? How to make the pivot table will expand follow by the expands of data? Create a dynamic named range for your source data - see http://www.contextures.com/xlNames01.html#Dynamic Rgds, Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Pivot Table
look at this :-
SourceData:= _ "Sheet1!R1C1:R6C4" You are "hard coding" the range for the source data. If you range named your data, say "MyData" then SourceData:= _ "Sheet1!MyData" or fi you know the last row..... SourceData:= _ "Sheet1!R1C1:R" & lastrow & "C4" Patrick Molloy Microsoft Excel MVP -----Original Message----- Hiyee all... I'm a newbie in programming with VBA. I had post this Q in "Excel-New User" but cant get the answer from there... So, I throw my Qeustion here... Hope someone can answer for me.. Thanks.. When I create the pivot table, I record the macro. After complete building the pivot table, I stop my macro. Hence, the next time I want to built my pivot table, I will just click on macro. When I recorded it with macro, I can built it with the same amount of data. The problem here is, when I expanded my data, I cant get the correct pivot table.. Below is the example... For eg: I got the below 5 data: SalesRep Region Month Sales Amy North Jan 33488 Amy North Feb 47008 Amy North Mar 32128 Bob North Jan 34736 Bob North Feb 92872 After I create the pivot table while recording the macro, it will show me the below macro code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 5/19/2004 by leecy3 ' ' ActiveWorkbook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _ "Sheet1!R1C1:R6C4").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable5", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Region ") .Orientation = xlPageField .Position = 1 End With ActiveSheet.PivotTables("PivotTable5").AddDataFie ld ActiveSheet.PivotTables( _ "PivotTable5").PivotFields("Sales"), "Sum of Sales", xlSum With ActiveSheet.PivotTables("PivotTable5").PivotFields ("SalesRep") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable5").PivotFields ("Month") .Orientation = xlColumnField .Position = 1 End With Sheets("Sheet1").Select End Sub After that, I can run the pivot table automatically by juz choosing the macro1. The problem here is, If I expand my data as below: SalesRep Region Month Sales Amy North Jan 33488 Amy North Feb 47008 Amy North Mar 32128 Bob North Jan 34736 Bob North Feb 92872 Bob North Mar 76128 Chuck South Jan 41536 Chuck South Feb 23192 Chuck South Mar 21736 Chuc South Jan 41536 Chuc South Feb 23192 Chuc South Mar 21736 and I click on macro1, it give me the same pivot table and both of the Chuck and Chuc data didnt include inside the pivot table. How can I modify the code in macro1 so that I can execute the macro1 that I recorded ? How to make it flexible and it wont fix on the size of the data? How to make the pivot table will expand follow by the expands of data? Hope to hear from whoever that know to solve this question... thanks --- Message posted from http://www.ExcelForum.com/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Pivot Table
Thanks Andy, Thanks Patrick Molloy !!!
And I would like to say thank you to BrianB too!!! I had manage to figure it out finally... with you all help! Thanks a lot. :) p/s: This topic can be delected : -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
Pivot Table in Excel 2007 : Problem with % in Table | Excel Discussion (Misc queries) | |||
Pivot table problem | Excel Discussion (Misc queries) | |||
Pivot Table Problem | Excel Programming | |||
Pivot table problem | Excel Programming |