Each month I have three seperate Excel spreadsheets each with about 6000 rows
and multiple collumns. I need to breakdown and distribute different portions
of the data to various people. These seperate reports need to subtotaled and
formated to print but are sent via email.
There is a collumn in the spreadsheet that I use to decide who gets each
report. Depending on what is noted in the collumn, I email the report to
different people.
The number of different reports is growing and I now have to create about 70
reports from each spreadsheet. I need an easy way to break each of these
spreadsheets down. I am currently creating the seperate spreadsheets and
using a macro to subtotal, format to print and save the file.
Is there an easier way using access or VBA. I am not very familiar with
either Acess or VBA but and a quick learner and am willing to get training on
whatever will make this easier.

You could create a custom view for each of the report layouts that you need.
It will be some work but if you're unfamiliar with Access this wouldn't be
overwhelming.

Look up Custom Views in help for details concerning the creation of a custom
view.
--
Kevin Backmann


"LoriH" wrote:

Each month I have three seperate Excel spreadsheets each with about 6000 rows
and multiple collumns. I need to breakdown and distribute different portions
of the data to various people. These seperate reports need to subtotaled and
formated to print but are sent via email.
There is a collumn in the spreadsheet that I use to decide who gets each
report. Depending on what is noted in the collumn, I email the report to
different people.
The number of different reports is growing and I now have to create about 70
reports from each spreadsheet. I need an easy way to break each of these
spreadsheets down. I am currently creating the seperate spreadsheets and
using a macro to subtotal, format to print and save the file.
Is there an easier way using access or VBA. I am not very familiar with
either Acess or VBA but and a quick learner and am willing to get training on
whatever will make this easier.

hi
i have not seen your data so i can only make vague remomendations.
you do seem to be approching overload so automations would appear to be your
only option at this point.
i would link your spreadsheets to access as a excel named range so that
access can use them as access tables. i would then write queries in access,
selecting(filtering) the data needed based on different criteria. queries are
not that hard to do in access. see this site...
http://www.utexas.edu/its/training/h...OPIA_AccessGS/

since your users are already use to receiving the needed data via excell i
would use microsoft query(MSQ) to link to the access queries to download the
queries into excel for Print/email. if done right you could reduce the whole
precess to 1 button click. I did it with 12 reports once. set up an ontime
macro to fire at 5am in the morning and run all the MSQs. when i got to work,
my reports were on the printer. of course, it did take a while to set it all
up but I shaved 4 hours a day off my workload.
my thoughts
FSt1

"LoriH" wrote:

Each month I have three seperate Excel spreadsheets each with about 6000 rows
and multiple collumns. I need to breakdown and distribute different portions
of the data to various people. These seperate reports need to subtotaled and
formated to print but are sent via email.
There is a collumn in the spreadsheet that I use to decide who gets each
report. Depending on what is noted in the collumn, I email the report to
different people.
The number of different reports is growing and I now have to create about 70
reports from each spreadsheet. I need an easy way to break each of these
spreadsheets down. I am currently creating the seperate spreadsheets and
using a macro to subtotal, format to print and save the file.
Is there an easier way using access or VBA. I am not very familiar with
either Acess or VBA but and a quick learner and am willing to get training on
whatever will make this easier.