Okay I need help writing this macro on something I do on a weekly basis, not sure how to do it. Keep in mind I’m in excel 2003 so the macro recorder isn’t as good as 2007. I’ve done a lot of simple macros, but nothing like this. I’m sure it’s simple, but it’s new to me and I am lost.
On a sheet called “List” in column C I have departments going down from C2:C44, in C45 I have the text “END”
Macro needed to be written:
1. In a sheet called “Model” in cell B1 take the first dept from the above sheet called “list” (link this cell to List C2 the first time, second time steps repeated List C3, ect)
2. Calculate/ F9 (rest of formulas in work book)
3. In sheet “Model” I need to do a regression on Y Input = $s$5:$s$160, X Input $az$5:$AZ$160, Confidence Level 95%, output range $Y$22
4. Execute regression
5. Calculate/ F9 (rest of formulas in work book)
6. Copy a calculated cell range which is formula driven after the regression is finished from “Model” “E5:E56”
7. Paste values and transpose into a sheet called “Data” starting in Cell D5 for the first dept. (2nd time D6, 3rd D7, ect)
Repeat above steps until step #1 dept reaches “End” also step 7 will continue pasting the values and transposing them down starting so that all depts are listed.
Who ever can walk me through this first gets a box of cookies mailed to them. PS I do this on Fridays so I should mail you a beer too!
-Vito-
On a sheet called “List” in column C I have departments going down from C2:C44, in C45 I have the text “END”
Macro needed to be written:
1. In a sheet called “Model” in cell B1 take the first dept from the above sheet called “list” (link this cell to List C2 the first time, second time steps repeated List C3, ect)
2. Calculate/ F9 (rest of formulas in work book)
3. In sheet “Model” I need to do a regression on Y Input = $s$5:$s$160, X Input $az$5:$AZ$160, Confidence Level 95%, output range $Y$22
4. Execute regression
5. Calculate/ F9 (rest of formulas in work book)
6. Copy a calculated cell range which is formula driven after the regression is finished from “Model” “E5:E56”
7. Paste values and transpose into a sheet called “Data” starting in Cell D5 for the first dept. (2nd time D6, 3rd D7, ect)
Repeat above steps until step #1 dept reaches “End” also step 7 will continue pasting the values and transposing them down starting so that all depts are listed.
Who ever can walk me through this first gets a box of cookies mailed to them. PS I do this on Fridays so I should mail you a beer too!
-Vito-
