G
Guest
Folks,
I’ve spent a fair bit of time perusing this and other groups. Two things
were very apparent to me. First, I don’t know much! Second, I’m so far out
I don’t even know what to ask. The questions that folks have been asking are
very detailed and targeted to a very specific task. My situation is
different. I need a plan, some general guidance on how to proceed. I need a
roadmap because I’m not even sure where I’m going. So, asking for detailed
directions at this point is senseless! Here’s the deal.
I have 4 workbooks (SOURCE.XLS, RECONSTRUCT.XLS, MODELS.XLS, RESULTS.XLS)
and each contains 88 worksheets, one for each management unit (county). The
exception is SOURCE.XLS.
SOURCE contains a single worksheet with 88 rows (counties) and 4 columns
(age classes). SOURCE contains buck (male deer) harvest figures for the
current year’s hunting season (2005 in this case).
RECONSTRUCT uses the buck harvest data in SOURCE to reconstruct/estimate the
size of the buck population just prior to the hunting season. Currently, I
have to paste the buck harvest data into each worksheet (88 cut and paste
operations) of RECONSTRUCT. There are a number of formulas that use the
harvest data within RECONSTRUCT to generate prehunt population estimates for
each age class (n=4). RECONSTRUCT consists of 2 4*30 matrices (age
class*year), a harvest data matrix and a population estimates matrix. Much
of the historical data (both harvest and population estimates) is used in the
calculations, so I must keep it. Presently, the following steps are
repeated, year in and year out: 1) Select the harvest data from SOURCE and
paste it into the harvest matrix in RECONSTRUCT and label it 2005 2) Go to
population matrix in RECONSTRUCT and copy most recent population estimates,
in this case 2004, into the 2005 column. The population values are updated
using the 2005 data from the harvest matrix. Once the buck population is
reconstructed for all 88 counties, the current year’s population estimates
for the buck population, along with the harvest data, must be cut and pasted
individually into MODEL.XLS. In addition to these 2 pieces of data, doe
(female) harvest data from SOURCE, must also be pasted into MODEL.XLS. These
harvest data (buck and doe) and buck population estimates are then used in a
series of calculations in MODEL to generate the prehunt doe population and
ultimately project the size of the total herd to the following year (in this
case, 2006). Like the RECONSTRUCT worksheet, MODEL.XLS contains roughly 30
columns, 1 for each year. However, it contains about the same number of rows
representing an array of various descriptive variables for the population.
The final phase of this annual undertaking is what I call extraction. Many
of the 30 variables calculated in each of 88 sheets in MODEL are needed for
further analyses and evaluation of trends. Thus, what I would like to do is
extract values for the most recent year for select variables and add them to
RESULTS.XLS, which would have 88 sheets (1 for each county) and say 10 rows
and 1 column for each year from 1977 to present.
So as I see it, I’ve got the following tasks to automate:
1) I need to get the buck harvest data out of SOURCE and update the harvest
matrix in RECONSTRUCT.
2) Within the population matrix of RECONSTRUCT I need to copy and paste the
most recent range into the current year’s column to generate buck population
estimates.
3) I need to extract the buck harvest and population estimates from each of
88 sheets and paste them into MODEL, but only after I copy the most recent
column and paste into the current year’s column within MODEL.
4) I need to extract certain cells from all 88 worksheets within MODEL and
paste them into the RESULTS.XLS spreadsheet for trend analyses.
I need to do this once a year. Presently, I’m cutting and pasting 88 times
for each of these operations and it is killing me. I know there is a better
way.
I apologize for the lengthy post, but I figured you would need the detail
sooner or later. Any help that you are willing to offer will truly be
appreciated. I also apologize for any cross-posts but I'm desperate!
Regards,
Mike
I’ve spent a fair bit of time perusing this and other groups. Two things
were very apparent to me. First, I don’t know much! Second, I’m so far out
I don’t even know what to ask. The questions that folks have been asking are
very detailed and targeted to a very specific task. My situation is
different. I need a plan, some general guidance on how to proceed. I need a
roadmap because I’m not even sure where I’m going. So, asking for detailed
directions at this point is senseless! Here’s the deal.
I have 4 workbooks (SOURCE.XLS, RECONSTRUCT.XLS, MODELS.XLS, RESULTS.XLS)
and each contains 88 worksheets, one for each management unit (county). The
exception is SOURCE.XLS.
SOURCE contains a single worksheet with 88 rows (counties) and 4 columns
(age classes). SOURCE contains buck (male deer) harvest figures for the
current year’s hunting season (2005 in this case).
RECONSTRUCT uses the buck harvest data in SOURCE to reconstruct/estimate the
size of the buck population just prior to the hunting season. Currently, I
have to paste the buck harvest data into each worksheet (88 cut and paste
operations) of RECONSTRUCT. There are a number of formulas that use the
harvest data within RECONSTRUCT to generate prehunt population estimates for
each age class (n=4). RECONSTRUCT consists of 2 4*30 matrices (age
class*year), a harvest data matrix and a population estimates matrix. Much
of the historical data (both harvest and population estimates) is used in the
calculations, so I must keep it. Presently, the following steps are
repeated, year in and year out: 1) Select the harvest data from SOURCE and
paste it into the harvest matrix in RECONSTRUCT and label it 2005 2) Go to
population matrix in RECONSTRUCT and copy most recent population estimates,
in this case 2004, into the 2005 column. The population values are updated
using the 2005 data from the harvest matrix. Once the buck population is
reconstructed for all 88 counties, the current year’s population estimates
for the buck population, along with the harvest data, must be cut and pasted
individually into MODEL.XLS. In addition to these 2 pieces of data, doe
(female) harvest data from SOURCE, must also be pasted into MODEL.XLS. These
harvest data (buck and doe) and buck population estimates are then used in a
series of calculations in MODEL to generate the prehunt doe population and
ultimately project the size of the total herd to the following year (in this
case, 2006). Like the RECONSTRUCT worksheet, MODEL.XLS contains roughly 30
columns, 1 for each year. However, it contains about the same number of rows
representing an array of various descriptive variables for the population.
The final phase of this annual undertaking is what I call extraction. Many
of the 30 variables calculated in each of 88 sheets in MODEL are needed for
further analyses and evaluation of trends. Thus, what I would like to do is
extract values for the most recent year for select variables and add them to
RESULTS.XLS, which would have 88 sheets (1 for each county) and say 10 rows
and 1 column for each year from 1977 to present.
So as I see it, I’ve got the following tasks to automate:
1) I need to get the buck harvest data out of SOURCE and update the harvest
matrix in RECONSTRUCT.
2) Within the population matrix of RECONSTRUCT I need to copy and paste the
most recent range into the current year’s column to generate buck population
estimates.
3) I need to extract the buck harvest and population estimates from each of
88 sheets and paste them into MODEL, but only after I copy the most recent
column and paste into the current year’s column within MODEL.
4) I need to extract certain cells from all 88 worksheets within MODEL and
paste them into the RESULTS.XLS spreadsheet for trend analyses.
I need to do this once a year. Presently, I’m cutting and pasting 88 times
for each of these operations and it is killing me. I know there is a better
way.
I apologize for the lengthy post, but I figured you would need the detail
sooner or later. Any help that you are willing to offer will truly be
appreciated. I also apologize for any cross-posts but I'm desperate!
Regards,
Mike