Solved: re-using macros and pivotTables

[Lester]

Hi
[I asked this question embedded within another thread, but maybe it should have a thread of its own, so here goes...]

I have created a macro and a PivotTable for displaying some data in an excel application.

Now, does anyone know what is the best way (if at all possible) to be able to use this new macro/pivotTable on a different (source) data file?

What would be the best way to do this? Would it be possible to 'import' the new source data into the existing file containing the macro, then re-run the macro against the new data? How about the PivotTable? Would that also be regenerated with the new set of input data?

I'm not sure the best way of utilising the macro/table for other sets of data that have the same 'structure' as my original set of data.

Any ideas? Please let me know.
Many thanks
Lester.

[ajdep]

I have a similar problem as yours. Using VBA code, I copied a master worksheet into a new worksheet and updated the data in 3 of the columns. I generated a dynamic define name referencing the new worksheet (=Dyn_10_23_2007_Portfolio).

I then created a macro to generate 4 pivot tables on a new worksheet (See partial VBA code for creating the pivot table below).

Range("H3").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"=Dyn_10_23_2007_Portfolio").CreatePivotTable TableDestination:=Range("H3"),_
TableName:="PivotTable2"

My problem is that I want to use this macro to generate new worksheet with different data based on the date. As you can see in the code above, "=Dyn_10_23_2007_Portfolio" is hard-coded in the macro itself so when I run it on a new set of data, it won't be pointing to the new worksheet. I tried using a variable in the code above, but get a VBA run-time error.

Does anyone have a solution to my problem maybe by using a range object?
Lester - how did solve your problem?

Thanks,

Tony

[Lester]

My code uses "With ActiveSheet", rather than explicitly naming any files.
I think what that means is that it takes the current worksheet as the source data.
This is my understanding of it...I am a complete novice to VBA, unfurtunately. Cheers

[ajdep]

Lester - Yes, I know I can do it within the current worksheet, but I prefer to have my pivots separate from my data worksheet since resizing the columns in the pivot won't affect the columns in the source data. If I can't find a solution, I may have to generate the pivots in the same worksheet. I would think there would be a way around this.

Thanks again for your response. I will keep searching. If I find a solution, I will let you know.

Tony