Problem with Pivot Table

[unplugs]

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:=xlDatabase, 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

 

[Andy Brown]

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

 

[Patrick Molloy]

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:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R6C4").CreatePivotTable

TableDestination:="", TableName:=

_
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard

TableDestination:=ActiveSheet.Cells(3, 1)

 

[unplugs]

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

: