Need to Revise Recorded Code for Pivot Macro

[twkyoscr]

Hi, I have set up a macro that creates a pivot table that needs to be run periodically with new rows of data but same fields each time. The first time the macro worked but now I am getting errors that say "runtime error 1004 reference not valid". How do I revise the code on the macro so that it will run each time? Here is what I have:

Public Sub Macro6()
'
' Macro6 Macro
' Macro recorded 11/24/2003 by ALEGENT HEALTH
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Selection").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array("Location", _
"Descr", "Data")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hrs")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Earns").Orientation = _
xlDataField
Application.CommandBars("PivotTable").Visible = False
End Sub

 

[SkipVought]

Hi,

There's not need to run your macro again.

1) Use the OFFSET function in Insert/Name/Define to define the range of your source data. This will make the range DYNAMIC no matter how many row are added or deleted.

faq68-1331 How can I rename a table as it changes size

2) Use the RefreshTable method in the Worksheet_Activate and Workbook_Open events

Private Sub Worksheet_Activate()
  For each pvt in Activesheet.Pivottables
    pvt.refresh.table
  next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAjedi]

If I understand you correctly, you want to create a new pivot table each time that summarizes a specific subset of the rows in your data (for example, the rows that were added this week, etc). I haven't done something quite like that before, and there may be a couple of challenges. For example, how do you get Excel to create a PivotTable from a non-contiguous range (since you need to include your header row). Also, if you're always going to create the PivotTables in the same place, you will probably have to write code to check for an old PivotTable there and delete it if found.

How are you wanting to specify the source data rows? Are you just selecting them? If so, are you selecting the whole rows, or just the actual columns that are part of your table?

VBAjedi

 

[SkipVought]

VBA,

Completely unnecessary!

The OFFSET function makes the process dynamic and subsets of data can be displayed within the existing PT.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[twkyoscr]

Let me explain further and hopefully briefly!
I am actually running a Peoplesoft nVision report that runs bi weekly for each pay period. I have a template or layout in Excel that is supposed to run a tabular report based on the query in Peoplesoft. I have created an nVision InstanceHook that runs the macro for the pivot table which is saved in the layout(template) and creates the pivot table totals when the nVision report is run automatically. My problem is the reference to the original excel file when I created the macro. I will try your suggestion Skip and any others that come along! Thanks

 

[SkipVought]

If you are running a query, chances are that the resultset is already in a NAMED RANGE. THAT NAMED RANGE is the name to use in the SourceData.

So if the NAMED RANGE is MyDatabase...

ActiveWorkbook.PivotCaches.Add( _
  SourceType:=xlDatabase, _ 
  SourceData:=[b]MyDatabase[/b]).CreatePivotTable TableDestination:="", TableName:="PivotTable1"

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884