then you should create separate workbooks that contain the information for
each manager. You still might be able to use Jim's suggestion and create
these sheets dynamically by using a pivot table and pivot chart. then copy
the sheet(s) to a new workbook and make them static (cell.copy then
cells.pastespecial xlvalues on each sheet). Then mail that special workbook
to a manager, then process the next one the same way - changing the values
that control the information displayed in the pivottables. It didn't sound
like you needed an interactive portion for the manager.
--
Regards,
Tom Ogilvy
"Nicole Seibert" wrote:
I have already created the data layer downloading data. I am still waiting
on a requirements doc, but ...
Two or three additional bits of information that may throw a wrench in it:
1. each set of three worksheets will be emailed to the manager
2. and due to its senstive nature I can not show any manager another manager
data.
3. There will be a bar graph on each sheet which I am assuming will take up
more memory than interactive sheets.
My current work around is to possible create several groups of data. I
would then close down sections of data and open new ones in sucession. Based
on my data it would make sence to do it in three groups.
Oh and I am using Windows 2000/NT with Excel 2003. I used your macro and
created 400 interactive sheets with only a minor glitch in that they were not
in order. Is this normal?
Any other ideas would be helpful.
Thanks,
Nicole
"Jim Thomlinson" wrote:
Since I don't know a whole pile about whate exactly you need I will give a
very general response... When creating the type of spreadsheet that you are
describing usually you are best off to separte the spreadsheet into layers.
One layer just holds data. The only person who ever sees it is you (the
developer) This will be a small handfull of sheets that contain the data for
all of the managers. The data is all kept in one place and there are few if
any calculations on these sheets. This is refered to as the data layer.
The next layer is the Business Logic Layer (this layer may or may not be
required). Lets say you have your source data on a few different data sheets.
Here you will do some of your calculations to manipulte and relate the data
from the different data sources to calculate final values that you wnat to
show to the end users. Normally this will be one sheet (or just a couple).
The final layer is the User Interface. This layer grabs information from
your Business logic layer and displays it to the end user. Usually it is a
bunch of Vlookups on sumproduct formulas. Once again it should only be a
handfull of sheets. It needs to be interactive to allow the user to select
each manager individually. Pivot Tables are also great for doing this...
The nice thing about doing it this way is that it will only be a hand full
of sheets (as opposed to 200+). If changes are required you can easily modify
it. If someone decides that they want a new report it is usually no big
deal.
--
HTH...
Jim Thomlinson
"Nicole Seibert" wrote:
Good question. I am automating the creation of reports spaning three
worksheet per manager. One worksheet houses definitions and will be pulled
from elsewhere. The first two worksheets will include sensitive information
for their eyes only and hence needs to be separated from other manager's
information. Also, we need to keep these records for a year-and-a-half.
There are over two hundred managers, but depending on the information not all
managers will receive a report; I am guesstamating that there will be at
least 200 sheets.
Thanks,
Nicole
"Jim Thomlinson" wrote:
Only bounded by memeory. Will your workbook crash with 200+ worksheets.
Depends how much stuff is on them. Here is a link to memory limits in Excel.
http://www.decisionmodels.com/memlimits.htm
My question to you would be what are you trying to do that is going to
require 200 worksheets? With that many sheets will the spreadsheet be usable?
--
HTH...
Jim Thomlinson
"Nicole Seibert" wrote:
Hello fellow helpers,
When I query Excel help as to the limit of worksheets in a workbook I get
the somewhat cryptic message that worksheets are limited by available memory.
Has anyone experienced any limitations on the number of worksheets? I am
trying to answer this question prior to coding a workbook with the
possibility of over 200 worksheets.
Thanks,
Nicole