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

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

To help you, it would help us to have a sample workbook. See yellow banner above.

Hello!

Yes, of course.

Please see the file Below. Thank you so much for the help!

Use Case.xlsb

Originally Posted by Norie

Here's a pretty basic way to do it.

Should work without problems but there's plenty that could be improved.

Please Login or Register  to view this content.

Hello!

Thank you so much, it works perfectly, you rock!! :D

The code is easy to interpretate, making it easy for me to do my minor adjustments.

Thinking on the 2.0 for this Macro, is it difficult/possible to add an "e-mail sending" funcionality, via Outlook, considering that the destination e-mails for each file are on column F of "Values" Sheet?

Best Regards,
NSP

Yes!

Each Workbook goes to a specific and different e-mail address, according to what is filled, for example, on column F of "Values" Sheet, like said above.

Originally Posted by Norie

Again, pretty basic - no error trapping, no fancy e-mail content/formatting etc. but seems to work.

Please Login or Register  to view this content.

Hello Norie,

Thanks again for your help. This time I am having a Run-Time Error -2147221080 (800401a8)

Do you have a clue why?

Thanks!
NSP

Originally Posted by Norie

What's the error message?

Hello!

Ther is no much detail... Tried again, and the error message is slightly different:

Attachment 827405