VBA formula that creates new workbook from 1 sheet after 1 cell is updated and refresh

[NSP27]

Hi guys!

I'm new in the VBA world, and I have a challenge on my hands, and having difficulties completing it.

I have a Excel sheet (Master) with some formulas depending on the value of a specific Cell (Example Cell B3).

In another sheet (Values) I have a list of values (Example Range A2:A30).

My goal is to build a macro that does the following sequence:

1 - Copy 1st value of the list (Sheet "Values" Cell A2)
2 - Paste on cell that has formulas depending on it (Sheet "Master" cell B3)
3 - Refresh Calculations (Shift + F9)
4 - Save a Pasted-Values Copy of the Master Sheet in a new Workbook named as the Value of the List copied on Step 1
5 - Loop the steps above for the remaining values of the Range A2:A30 until it uses all values from the range.


Can you please help?

Thanks a lot!
NSP

 

[OaklandJim]

Folks willing to assist probably need a more/clearer statement of need.

Plus, without the workbook someone would have to create one to assist. Consider posting a link to the workbook using Box, Dropbox, 1Drive, Sharepoint. Also, consider using Mr Excel's excellent add-in called XL2BB which enables you to post portions of a workbook so it is not necessary to recreate fake data. See HERE for details.

When you say

2 - Paste on cell that has formulas depending on it (Sheet "Master" cell B3)

Do you mean code would need to sift through formulas in Master to find one/more than one formula that refers to cell A2 in Values?

Regarding

4 - Save a Pasted-Values Copy of the Master Sheet in a new Workbook named as the Value of the List copied on Step 1

Saving a workbook with the pasted values is not challenging but what is meant by Value of the List copied? Is that the value in cell A2 (for first loop/workbook saved)? So if A2 contains 345 then the new workbook is named 432.

Regarding

5 - Loop the steps above for the remaining values of the Range A2:A30 until it uses all values from the range.

I assume that you meant A3:A30? Does the range to be processed change? From the example there are 30 new workbooks?