Macro to copy cell value, paste to another cell, calculate, then copy worksheet

jjones81

New Member
Joined
Jan 24, 2010
Messages
2
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,148,424
Messages
5,746,597
Members
424,032
Latest member
pochie2741

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top