[b]Pivot Table in a new Workbook[/b]

[qureshi]

Hi,
I am trying to create a program to generate pivot tables automatically. The workbook that contains the data has 37 worksheets in it and I want to create pivot tables for all of them.
The problem I am facing is that the pivot tables are been created in the same workbook. This means that this workbook will end up with 37+37 sheets.

Is there anyway I can create the pivot tables in a new workbook.

Thanks in advance

 

[Gavona]

I am not sure you can create them in another workbook. BUT you can move them to another workbook after they have been created.


Gavin

 

[SkipVought]

Hi,

Are all 37 tables entirely different data structures, or are some or all of them similar structures with different data?


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[qureshi]

All the 37 tables are of the same structure. For now what i have done is like this:
copied the orignal file so that i can work with a copy of the data, processed the pivot tables and then deleted the orignal tables.

But there must be another way.

 

[SkipVought]

Q,

If you have data that could be in a single table, chopped up into 37 different sheets, it's insane.

It would be way easier to write code to consolidate the source data and make a single pivot table.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[qureshi]

The 37 different worksheets correspond to the 37 different products we manufacture so they have to stay like that, but I have to produce a solution for producing pivot tables.

The have overcome this problem.
If someone goes in any of the sheets and selects a cell say BI29 and just enters a space into it, the program cant get the used range anymore. Any suggestions in this regard will be appreciated.

Thanks

 

[SkipVought]

"The 37 different worksheets correspond to the 37 different products we manufacture so they have to stay like that ..."

WRONG!

In a consolidated table, have a column for the PRODUCT. In other words, each sheet name is a DATA ELEMENT.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]