Problem with Pivot Table

  • Thread starter Thread starter unplugs
  • Start date Start date
U

unplugs

Hiyee all~~
I'm a newbie of Excel with VBA. I got a question that need help fro
you all.

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. For example, my data initially from R1C1:R10C4
and I modify it to R1C1:R20C4, I cant get the correct pivot table buil
from the macro. What can I do to it? Anyone know about it?

I know I need to modify somewhere in the vba coding part. I had trie
it, but got errors due to my unfamiliar to this application program.

Anyone can guide me about this? Thanks
 
There is the occasional bug in pivot tables, but it is more likely tha
you have not included a line of code to Refresh the pivot table
Something like this :-

'-----------------------------------------------------
ActiveSheet.PivotTables(1).RefreshTable
'-----------------------------------------------------

You can save yourself some trouble by using a Dynamic Range name, ie
one that changes according to the amount of data. All you need to d
then is add rows to the bottom of your data sheet and refresh the pivo
table.

1. Copy this formula (which I have set for your data) :-

= OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)

2. Excel menu Insert/Name/Define
Paste the formula into the Refers To box.
Change Sheet1 to the name of your data sheet.

3. Type in a name for the range. Click OK.

4. Click in you pivot table. Data menu- Pivot Table Report.

5. The PT Wizard appears. Click "Back" button. and type just the nam
you have chosen for your range instead of the existing one.

6. Click on Next to .......... Finish
 
Thanks BrianB for your advise. Anyway, I hope u can explain to me ste
by step using the following 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
Wit
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, after I record the macro1, it can juz execute th
5 data. If I expand my data to the 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 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
 
Back
Top