I apologize in advance for this lengthy question. I'm trying to use a macro to automate a monthly routine I perform. I have a
workbook named “My Model.xls” containing a number of worksheets named vintage 1, vintage 2, etc. Each month I receive a file
named “Monthly Report.xls” formatted exactly the same as “My model.xls” with exactly the same tab names and each tab is also
formatted exactly the same, i.e. data in columns and rows are the same, except with one additional monthly information, which
I need to copy and paste value into “My model”.
In this example, this worksheet named “vintage 1” in “My
Model.xls” has columns containing monthly data of “200701, 200702, 200703, 200704” from column C to column F. In the “Monthly
Report.xls” file with same worksheet name “vintage1”, it contains one additional monthly data “200705” in Column G
I need to:
- Find the worksheet named “vintage1” from “Monthly Report.xls”
- Copy last column data “200705” in column G
- paste value into Column G in worksheet named “vintage1” in “My Model.xls” immediately after the last column’s data, i.e.
- Repeat the above process for all the tabs, i.e., vintage1 to vintage20.
The number of columns are different in
different worksheets. For example, while worksheet “vintage1” contains data from 200701 to 200704, i.e. from Column C to
Column G, worksheet “vintage 2” may contain data from 200608 to 200704 (i.e. Column C to K), and therefore the last column
that containing data are different for different vintages. Therefore for vintage1 I need to paste the new 200705 data into
column G, and for vintage2 in this example I need to paste into column L.
I’m sorry I was unable to attach an
excel worksheet example with this question. I can email you the attachment if necessary. Any help will be greatly