Paste value to another worksheet

tnjim

New Member
Joined
Feb 9, 2004
Messages
4
I am relatively new to excel so please pardon if this is silly question. I have a worksheet (Sheet1) that has formulas to pull in numbers from another worksheet (Sheet2)based on certain criteria. I want to copy the results of the formulas to a third worksheet (FR2002-03). However, when I copy the formula results from sheet1 to the FR2002-03 worksheet I have to put the actual value in the cells because this sheet is then sent to another agency and they will not allow formulas to be in the cells (they require it to be values). I have recorded a macro to copy, pastespecial, values which will work but it leaves me with a disadvantage if I ever insert a row, etc..., I will have to modify the range.

Example:

Sheet1: Cell F11 contains this formula
=INDEX(Sheet2!$D$1:$D$2500,MATCH(J11,Sheet2!$G$1:$G$2500,0))

This formula returns a result of = 222,364

I want to get the value of 222,364 into cell F11 on worksheet FR2002-03.

I appreciate any help I can get. Thanks. :eek:
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

earlyd

Well-known Member
Joined
Dec 10, 2002
Messages
1,199
Anytime you work with cell references in macros, name the cells and use the range names in the macro where possible. Then you can insert rows and columns without worry. For example if you name cell A5 MyValue then you can reference Range("MyValue").value.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top