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