Hi all,
I currently have a workbook with three worksheets, called "IS_Model":
1. "Data" - a tab with a data table that lists key income statement data for various locations over time, specifically location number in column A, year in column B, month in column C, Income statement line in column D, $ value in column E.
2. "IS" - a simple income statement summary, where a location number is input in cell A1, and then the income statement data for that location is pulled into the income statement summary by using formulas.
3. "Locations" - a simple reference tab that lists location numbers 1001-1050 in cells A2-A51 (+ a "Loc number" column heading in A1), so 50 locations in total.
What I'm trying to do is create a macro that will:
1. copy the first location number from "locations" sheet cell A2
2. paste the value into "IS" sheet cell A1
3. calculate
4. make a copy of the entire sheet, create a new workbook, let's call it "book2", and move the copied sheet into book2
5. break the links in book2
6. rename the sheet to the value in cell A1
7. go back to the "IS_Model"
8. select the next location number from locations sheet, so cell A3
.... and then repeat steps 1-8 until all locations have been looped through the process.
For steps 4 and 5, I want all the copied sheets to be moved to the same book2.
Hopefully that all makes sense. Although there are several steps, the process doesn't seem too complicated. I can clunk through writing macros but right now I'm getting caught up in creating the loop.
Any help appreciated. Thanks in advance.
I currently have a workbook with three worksheets, called "IS_Model":
1. "Data" - a tab with a data table that lists key income statement data for various locations over time, specifically location number in column A, year in column B, month in column C, Income statement line in column D, $ value in column E.
2. "IS" - a simple income statement summary, where a location number is input in cell A1, and then the income statement data for that location is pulled into the income statement summary by using formulas.
3. "Locations" - a simple reference tab that lists location numbers 1001-1050 in cells A2-A51 (+ a "Loc number" column heading in A1), so 50 locations in total.
What I'm trying to do is create a macro that will:
1. copy the first location number from "locations" sheet cell A2
2. paste the value into "IS" sheet cell A1
3. calculate
4. make a copy of the entire sheet, create a new workbook, let's call it "book2", and move the copied sheet into book2
5. break the links in book2
6. rename the sheet to the value in cell A1
7. go back to the "IS_Model"
8. select the next location number from locations sheet, so cell A3
.... and then repeat steps 1-8 until all locations have been looped through the process.
For steps 4 and 5, I want all the copied sheets to be moved to the same book2.
Hopefully that all makes sense. Although there are several steps, the process doesn't seem too complicated. I can clunk through writing macros but right now I'm getting caught up in creating the loop.
Any help appreciated. Thanks in advance.
