OK. Could you post your code so we can see what it is doing?

Here is my code (using macreo recorder).

Sub By_Product_and_by_Branch()
'
' By_Product_and_by_Branch Macro
' Macro recorded 5/25/2007 by XXX
'

'
Dim pt As PivotTable
Dim ps As PivotCache
Dim ws As Worksheet
Set ws = Worksheets("Pivot")
For Each pt In ws.PivotTables
pt.TableRange2.Clear
Set ps = Nothing
Next pt


Sheets("Pivot").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R330C16").CreatePivotTable TableDestination:= _
"'[2007 OPA Auditor Summary_MTY.xls]Pivot'!R4C1",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTables("PivotTable1").PivotCache.RefreshOnFileOpen = True
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"Process Area", "Review Area"), PageFields:=Array("Branch",
"Product")
With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Compliance %")
.Orientation = xlDataField
.Caption = "Average of Compliance %"
.Function = xlAverage
.NumberFormat = "0.00%"
End With

ActiveSheet.PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable1").Format xlTable9

ActiveSheet.PivotTables("PivotTable1").PivotFields("Process
Area").AutoSort _
xlDescending, "Average of Compliance %"


With ActiveSheet.PivotTables("PivotTable1").PivotFields("Review Area")
.Orientation = xlColumnField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Process
Area")
.Orientation = xlRowField
.Position = 1
End With

ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.1", Formula2:="0.6"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.6", Formula2:="0.8"
Selection.FormatConditions(2).Interior.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.8", Formula2:="1"
Selection.FormatConditions(3).Interior.ColorIndex = 50
Sheets("Pivot").Select
Range("B6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.1", Formula2:="0.6"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.6", Formula2:="0.8"
Selection.FormatConditions(2).Interior.ColorIndex = 45
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.8", Formula2:="1"
Selection.FormatConditions(3).Interior.ColorIndex = 50
ActiveWorkbook.ShowPivotTableFieldList = False
Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.1", Formula2:="0.6"
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.6", Formula2:="0.8"
Selection.FormatConditions(2).Interior.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlBetween, _
Formula1:="0.8", Formula2:="1"
Selection.FormatConditions(3).Interior.ColorIndex = 10
Range("E20").Select
Columns("D:D").ColumnWidth = 9.86
Columns("C:C").ColumnWidth = 11.57
Columns("E:E").ColumnWidth = 11.29
Columns("F:F").ColumnWidth = 11.57
Columns("H:H").ColumnWidth = 12
Columns("B:B").ColumnWidth = 9.57
Columns("B:B").ColumnWidth = 12.14
Range("D1").Select
Columns("C:C").ColumnWidth = 11
Range("D5").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Pivot").Range("D5")
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.ChartArea.Select
Sheets("Pivot").Select
Range("B15").Select
End Sub

The macro  is probably doing a whole heap of unnecessary things. Can you
describe a bit more what you are doing and why you need to run  a macro. If
you are using the same data source and the values have changed you should be
able to just do a refresh. If you have similar data and want to use the new
data (perhaps a different sheet) then you only need to attach the new source.

Thanks for the reply. My task is : I have one data sheet  that will be
the source for my pivot  tables and charts. I have created  a new
worksheet that has various buttons. The macros  run when the user
clicks the button. The macro  code that i sent you is a specific view
(Based on branch and by product).If the user clicks on "Branch and by
Product", then this macro should run  the worksheet called  "Pivot"
should be populated. If the user clicks on some other button (for some
other view, say Branch and by requirement area), then the same "Pivot"
sheet should be populated by a different set of data (but from the
same source). Also, in addition to generating the pivot table  report,
I would also like to have a pivot chart. So each time, when I click
the button, i get a new chart  and the old chart show  no values. I just
want a new table report  and one new chart.

I know that the macro is not efficient because it is a recorded macro
and does some needless tasks. I will work on cutting the redundant
tasks. But if you could help me in generating only one chart, that
will be great.

I am not an expert in pivot  charts and I am sure someone else will be able to
help you more than I can (you may need to wait until it is Monday in the US
and they are back at work.!)


I would be fairly certain you do not need to generate a new chart  each time.
You need to change the columns and rows of the pivot table  which is used as
the source and then (probably) refresh the chart. You may need to change the
source definitions for the chart itself.

I do not have the time  to try to build a test application at the moment and,
just now, I do not have any charts  built on pivot tables I can experiment
with.

As I said, I am sure someone who is experienced in this stuff will help you.

Yeah. I can understand that. Let me wait. Thanks
for your time  in looking into this. I appreciate it.

I have noticed on projects I have generated;
the pivot  charts will update as the pivot table  is updated.
As far as I can tell, the chart  'follows' the table.

Yeah. The pivot  charts gets updated each time  when i run  the macros  to
generate the pivot table  (because pivot tables and charts  are always
tied together). But while I can use only one worksheet for displaying
the pivot table, can i just use only one chart  every time I generate
the worksheet. Please see the description of the problem that i posted
previously.

I think the process you should be aiming for is modifying the pivot  table,
rather than creating it each time. Then it will stay connected to the chart.

This one gets interesting!
just for logistics;
Data column (A)='Branch'
Data column (B)='Product'
Data column (C)='Requirement Area'?

Please advise on data fields or column names.

Yeah. There are a lot of data columns. Let us say:
Data column (A)='Branch'
Data column (B)='Product'
Data column (C)='Requirement Area'
Data column (D)='Process Area'
Data column (E)='Level'
So on till Data column (M).

So that makes we can view the data depending on what we wanted to see.
For e.g, the macro  that I wrote is for "branch vs product view".
Similarly there will be multiple views that an user can choose. The
choices will be in a main sheet. when a user clicks on one button,
that particular view macro will run  and the result will be dissplayed.
The pivot table  will be created  and dispalyed on one sheet  called
"Pivot". That works fine. But similarly i want just one chart  only.
When the user clicks a dfiferetnt view, then along with the change in
pivot table, the same chart should also change. I dont want a
different chart to be created each time.

one simple solution:
Create separate pivot  tables (& charts) for the combinations of 'column'
selections; looks like 3 (ColumnA, ColumnB & ColumnC). Set up as usual, then
hide all but 1 pivot & chart.
Your Macro would then hide & unhide sheets (pivots & charts) depending on the
selection of primary & secondary data views:

If 'Data by Branch then by Product' selected then unhide sheets that contain
this view, hide all others.
If 'Data by Branch then by Requirement Area' selected then unhide sheets that
contain this view, hide all others.

Thanks for that quick smart methods. Let me try that. I think that
should work fine.