I'm working with a 100+ column spreadsheet that management would like
to see around 50 different reports generated from these columns, with
various sorts, subtotals and totals. I was looking at using Custom
Views and Report Manager to hide/show the columns I need for various
reports, then execute some VBA code to create all the sorts, subtotals
and totals I need.

I thought that another way to do this would be to have a hidden
duplicate 100+ column sheet that is used to setup the data sheet. All
the 50 reports that are needed would be listed here, with option data
in appropriate columns like the Sales Report might have a SRT1 (first
column to sort) in column J, then SRT2 (second column to sort) in
column B, etc. There might be a SBT6 (subtotal this column using
column 6 as the "change" column) in column Z. I think you get the
idea. I haven't worked out all the option coding for this but as
reports are added and removed it seems it would make my job easier by
having this hidden setup sheet. The setup sheet would be read into an
array when the file is loaded and a userform would popup asking what
report the user would like to see using buttons or dropdown box.

I was looking on the web for some example of this but found nothing.
Anyone know of any site that has an example of this? Is there an
easier way to do this? Would using Report Manager be the better way to
go?

Thanks.

RC

Have you ever thought about AutoFilter, and then using subtotals. It
lets you do a lot of cool things.

I have another management style report that has lots of auto filtering
and subtotals. It's just a pain when more reports are added or
columns are added/removed or subtotal changes are made. I spend too
much time in the code. I'm looking for an Excel Report Builder
Template that can be used on many Excel column/row type reports. Less
hardwiring of code and more of just changing a configuration sheet. I
would think someone would have done this before.

RC,
Not sure how feasible it would be, but you can write to the VBA IDE,
creating a custom function for the sort/sub total/format each time, using
your hidden setting sheet for input. You would need to write a kind of
parser to read your input and translate into VBA statements.

Then execute the function/sub that you have just written.
Once complete, delete the function (if required), ready for the next report.

NickHK

"RC" wrote in message
oups.com...
I'm working with a 100+ column spreadsheet that management would like
to see around 50 different reports generated from these columns, with
various sorts, subtotals and totals. I was looking at using Custom
Views and Report Manager to hide/show the columns I need for various
reports, then execute some VBA code to create all the sorts, subtotals
and totals I need.

I thought that another way to do this would be to have a hidden
duplicate 100+ column sheet that is used to setup the data sheet. All
the 50 reports that are needed would be listed here, with option data
in appropriate columns like the Sales Report might have a SRT1 (first
column to sort) in column J, then SRT2 (second column to sort) in
column B, etc. There might be a SBT6 (subtotal this column using
column 6 as the "change" column) in column Z. I think you get the
idea. I haven't worked out all the option coding for this but as
reports are added and removed it seems it would make my job easier by
having this hidden setup sheet. The setup sheet would be read into an
array when the file is loaded and a userform would popup asking what
report the user would like to see using buttons or dropdown box.

I was looking on the web for some example of this but found nothing.
Anyone know of any site that has an example of this? Is there an
easier way to do this? Would using Report Manager be the better way to
go?

Thanks.

RC

Thanks Nick. That sounds like it would work. I just don't want to
reinvent the wheel if I don't have to. Looking for a basic report that
someone has already done like this. A generic Excel Report Writer that
fits all or mostly all.