A Macro that Populates Cells with a Formula

Hi everyone, I have what should be an easy-to-answer question, but all my Google searches keep going in unproductive directions.

Suppose in my worksheet, I have a tab named "FRUIT":

ITEM	ID_NUM	PRICE	RATING
Apples	1001	2.00	20
Oranges	1002	3.00	15
Bananas	1003	1.50	30

...and so on. The purpose of "FRUIT" is to list all the fruit that my grocery store might carry. This information is static; it is never meant to be changed.

Now suppose I have a second tab named "PLAN ORDER". This tab is supposed to pull data from the cells of "FRUIT" and allow the user to manipulate them:

ITEM	ID_NUM	PRICE	RATING
=IFERROR(VLOOKUP($B1,'FRUIT'!$B$1:$D$4,1,0),"")	=IFERROR(VLOOKUP($B1,'FRUIT'!$B$1:$D$4,2,0),"")	=IFERROR(VLOOKUP($B1,'FRUIT'!$B$1:$D$4,3,0),"")	=IFERROR(VLOOKUP($B1,'FRUIT'!$B$1:$D$4,4,0),"")
=IFERROR(VLOOKUP($C1,'FRUIT'!$B$1:$D$4,1,0),"")	=IFERROR(VLOOKUP($C1,'FRUIT'!$B$1:$D$4,2,0),"")	=IFERROR(VLOOKUP($C1,'FRUIT'!$B$1:$D$4,3,0),"")	=IFERROR(VLOOKUP($C1,'FRUIT'!$B$1:$D$4,4,0),"")
=IFERROR(VLOOKUP($D1,'FRUIT'!$B$1:$D$4,1,0),"")	=IFERROR(VLOOKUP($D1,'FRUIT'!$B$1:$D$4,2,0),"")	=IFERROR(VLOOKUP($D1,'FRUIT'!$B$1:$D$4,3,0),"")	=IFERROR(VLOOKUP($D1,'FRUIT'!$B$1:$D$4,4,0),"")

I'm reasonably sure I've got that right. The above formulas should pull all the cell data from "FRUIT" to "PLAN ORDER". So once the above formulas are run, "PLAN ORDER" should actually look identical to "FRUIT", at least from the user's perspective:

ITEM	ID_NUM	PRICE	RATING
Apples	1001	2.00	20
Oranges	1002	3.00	15
Bananas	1003	1.50	30

Okay; from here, the user can change cell values, play around with the data, etc. My idea is that "FRUIT" would have permanent, static data, and and "PLAN ORDER" would be a copy of the data for the user.

But what if the user wants to restore the original data in "PLAN ORDER"? After all once they start changing values, they are overwriting the original formulas. I need a micro that, when activated, simply rewrites the original formulas back into the "PLAN ORDER" cells:

Please Login or Register  to view this content.

But for the life of me, I can't figure out the syntax. Does anyone see where I'm going wrong? Thank you.

Something like

Please Login or Register  to view this content.

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.