Export to Excel - Multiple Sheets over Dimension

[Anonymous]

Hello all,

What I'm trying to do is to take a table file which would normally be exported as one sheet in excel and instead to export it to multiple sheets whenever a particular dimension changes.

Here is a sample of what some data might look like:

@1	@2	@3
A	121	Sampletext1
A	122	Sampletext2
A	123	Sampletext3
B	121	Sampletext4
B	122	Sampletext5
B	123	Sampletext6
C	121	Sampletext7
C	122	Sampletext8

and here is how I'd like the Excel file to look, whereby each tab is labeled as the different change in dimension and only that relevant data shows up:

Can anyone provide some advice or sample code that might guide me to this type of solution?

Thanks,

- Peter

[Anonymous]

Hey Peter,

Here's a macro example I whipped together with your data. It works alright but the sheets get a bit mixed up when adding more values to "reduce" over. At least you get the idea.

[Anonymous]

Johannes,

Thanks for the quick response. This is helpful.

I was wondering how this might be replicated on a table that already has selections applied? When I run this macro it seems to go through and create it's own selections, however, if I have selections already applied to a table/chart I'd like to be able to export that and do something similar to this.

Thanks,

- Peter

[Anonymous]

Hey Peter,

Not quite sure what you mean. If I select B&C and export, I get an Excel file with B and C in different sheets. Do you want to keep the original selection after completing the macro? In that case you could create a bookmark in the macro before looping over the values, and recall it after finishing before dropping the bookmark.

I modified the macro quickly. See attached example.