Copy/Pasting Macro Function

Hello, I have added macros to your worksheet to do this.
1) Open your file & SaveAs to NEW TEST file. Open my attachment.
2) Go to VB Editor. Double-click on the Sheet1 Module of my attachment. In the Code window, you will see new code (two procedures: Worksheet_Change and Update_Model_Value).
3) Swipe over all of this text with your mouse (highlite EVERYTHING in blue, top to bottom). Copy to PC buffer (Ctrl+C).
4) In VB Editor, DOUBLE-Click on Sheet1 of your NEW TEST file. The code window here should be blank. Plant cursor in code window and paste clipboard here (Ctrl+V).
5) Save file.
6) Now you need to build NINE (9) different RANGE NAMES in your TEST file as follows:
6a) Go to F4 (sheet1), then type "Model_Cell" (w/o quotes) in Range Name field and hit enter.
6b) Select cell AB6-AB21, sheet1. Type "MinData_Source" in Range Name field, enter.
6c) Select cell AC6-AC21, sheet1. Type "MaxData_Source" in Range Name field, enter.
6d) Select cell E86-E101, sheet1098. Type "MinData_Trgt1098" in Range Name field, enter.
6e) Select cell E151-E166, sheet1098. Type "MaxData_Trgt1098" in Range Name field, enter.
6f & 6g) Ditto for sheet 1190 typing "..._Trgt1190"
6h & 6i) Ditto for sheet 1220 typing "..._Trgt1220"
7) Save file again. It should work for you at this point.
8) This macro will execute automatically whenever you change value in F4 of sheet1. Alternatively, you could assign a Shortcut Key to the Macro (such as C+S+M) to drive the "copy" macro. But with the Worksheet_Change macro, you don't have to do this, as it will automatically see change and drive "copy" macro for you.
9) Study code over & test out on TEST file. Make sure it does what you want. Change accordingly. Holler back if you get stomped on anything.
... Regards, sauerj

1) Glad to help ... the macro was failing because your Target Range Names had gotten corrupted (i.e. they had #REF in their references). You probably named everything OK, but then you might have moved or copied something which caused these 6 Range Names to loose their reference. I edited your copy and am sending it back to you (see attached).
2) Range Names may sound like a hassle, but they are better than putting hard cell addresses in your code. If the latter, then every time you insert columns or rows, or move your referenced cells, the code must be manually adjusted (major pain!). With Range Names, if you are careful, typically they hold fast in staying with the appropriate cells.
3) The Range Name editor in Excel is VERY lacking. If you want to manage Range Names, I HIGHLY recommend downloading the following "Name Manager": (http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp). ... Regards, sauerj

Hey SauerJ,

The attached file says "Cannot Run the Macro" when I click the "Record Data" button

Any idea why that is happening?

Thank you, and as always for your time as well
Bob

Bob, That's a wierd one! ... Sounds like you meant to attach a file, but don't see it. Edit last post or add new post to include attachment. ... sauerj

Hey Sauerj, I meant with the file in the last message you sent says "Cannot Run the Macro" when I click the "Record Data" button after adding in the values in worksheet "Sheet1"......here is the file with the values ready to be inserted right before clicking the "Record Data" button

Any idea why this message would come up?

Thank you,
Bob

Bob, I apologize for my last post. I mis-read your previous post. When you said, "Record Data" button, I mis-read this as "Record Macro" button. So, I thought you were getting this message when you were trying to Record a new Macro. That's why I thought this was a real wierd one.
Now that I see that you have a Macro Button on your worksheet labeled "Record Data", I now understand your issue. Sorry!
Fortunately, this is an easy fix. Maybe you have figured it out already. If you RIGHT-CLICK on the "Record Data" button and select ASSIGN MACRO, you will see that the button has a macro assigned called #N/A. But, this is not a macro in the list of available macros to run. So, all you have to do is select which macro of the list of available macros you want to execute when you hit this button. I assume you want the "Sheet1.Update_Model_Value" macro. Once you have made this macro assignment, then it should work for you. ... Holler back if this doesn't fix it or if you have further issues. Take care, Joe.

Thank you JOE!!!