VBA to save new files based on worksheets

[Access Beginner]

Hi Guys,

I have a workbook which will use the Show Report Filter as pages option for a pivot table to create 16 sheets based on the filter. I also have another worksheet called "Cover Page".

What I would like (if possible) is to create 16 new files ( xlsx format) based on the 16 sheets, but to also include the "Cover Page" in these newly created files.

The 16 sheets names will change depending on which filter I apply in the pivot table. But I can change the the names of the code when needed, if soemone can supply it in the 1st instance.

Any help is greatly appreciated, as I have to create these reports based on new data each week.


Using Excel 2007

Cheers

 

[Access Beginner]

Hi Jeff,

These Zone reports, have data by site and I've just noticed that some of the Zones have incorrect sites included in the new created files.

Example: Site abc belongs to Zone 3 Central New South Wales, but when teh code is run, Site abc is included in the Zone 2 xxxx

There are a couple of reasons why I think this may be happening:

1. Some Zones have no data for them. The data set for September there are 2 Zones that don't have any data.
2. Some of the Zone names when created via the "Show Report Filter Pages" are truncated eg Zone 3 Central New South Wales, when this sheet is created via the above it is called Zone 3 Central New South W.

I'm not sure if these are the reasons and if they are, can they be solved.

Cheers
Haydn

 

[Jerry Sullivan]

These Zone reports, have data by site and I've just noticed that some of the Zones have incorrect sites included in the new created files.

Example: Site abc belongs to Zone 3 Central New South Wales, but when teh code is run, Site abc is included in the Zone 2 xxxx

There are a couple of reasons why I think this may be happening:

1. Some Zones have no data for them. The data set for September there are 2 Zones that don't have any data.

The issue of what to do about displaying combinations of Pivot Table fields that have no values, is more complex than one might expect.
See this thread for a discussion and some options....
http://www.mrexcel.com/forum/showthread.php?p=2915068

2. Some of the Zone names when created via the "Show Report Filter Pages" are truncated eg Zone 3 Central New South Wales, when this sheet is created via the above it is called Zone 3 Central New South W.

Is the Sheet name truncated, or the pivottable field item or both?

 

[Access Beginner]

Hi Jerry,

Thanks for the reply, I'll have read of the thread and see what I can glean from that.

Is the Sheet name truncated, or the pivottable field item or both?

The Sheet name only is truncated.

Cheers
Haydn

 

[Jerry Sullivan]

Hi Jerry,

Thanks for the reply, I'll have read of the thread and see what I can glean from that.


The Sheet name only is truncated.

Cheers
Haydn

The last version that you posted didn't have any code to change the sheet name. Did you add something?

If you want to name each sheet the same as the report page filter you could add this....

    ActiveSheet.Copy 'copies PT Report sheet to New Book
    ActiveSheet.Name = sItem      'rename sheet
    With ActiveWorkbook

 

[Access Beginner]

The issue of what to do about displaying combinations of Pivot Table fields that have no values, is more complex than one might expect.
See this thread for a discussion and some options....
http://www.mrexcel.com/forum/showthread.php?p=2915068



Is the Sheet name truncated, or the pivottable field item or both?

and my apologies for referring to you as Jeff, no excuse other than a lack of attention to detail.

 

[Jerry Sullivan]

and my apologies for referring to you as Jeff, no excuse other than a lack of attention to detail.

No worries, mate.