Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.