Free Microsoft Excel 2013 Quick Reference

Unable to copy formula into another cell in same sheet

Hello all,

I'm using excel 2007 and I'm unable to copy formula from one cell to another cell in same sheet.

for example if I put some formula in A2 cell and now I copy it from A2 cell by Ctrl+C and simply paste it by Ctrl+V into B2 it copy A2 cell value instead of formula.

Post your answer or comment

comments powered by Disqus
Hiya, I have been playing around with the macro record button, but im coming
a little unstuck in a couple of places.

I am trying to assign either of the two formula into every cell in column H
too join the text values together in a single value.


although i know that either of these formula will do the job, i am not
seeing the value displayed, the formula is staying displayed.

Also, i would like to know how to insert two, columns at once, say after E,
and how to join the Text to Columns function that i currently have being
done in two processes so that it can be done in one process.

Here is the code i have so far.

Selection.ColumnWidth = 5
Selection.ColumnWidth = 12
Selection.ColumnWidth = 32
Selection.ColumnWidth = 10
Selection.ColumnWidth = 8
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Selection.Insert Shift:=x1ToRight
Selection.Insert Shift:=x1ToRight
Selection.TextToColumns Destination:=Range("E1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(2, 2)),
Selection.TextToColumns Destination:=Range("F1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(1, 2)),
Selection.ColumnWidth = 4
Selection.ColumnWidth = 2
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 10
ActiveCell.FormulaR1C1 = "=E1&""""&F1&G1"
End Sub

I know how to copy info into blank cells within a column going down the
column. The blank cell fills in with the info till the info changes . . .
But, how can I go up the column instead of down?
Example, blank cells are A2 and A4 and the information I want copied is in
A6. Blank cells are A8, A10, A12, A14 and information I want copied is in
A16 . . .

how do I get data from an external source to copy directly into assigned
cells in Access or Excel. I am creating a mailing list and I am sure that
there is a way to copy from my source that is divided in to cells and have
the fall where I want it to in my spreadsheet. Is there a command or

How to copy text from another cell and replace space with underscore

is this possible?

how to Show Formula of one Cell in NEXT CELL,

I mean i have formula in cell A2 = degrees(B2)





I'm trying figure out how to copy text from a "list" (i.e., 4 or 5 text
entries in one column, but each in individual cells) to another cell in the
same spreadsheet by clicking on the original cell just once. The text always
needs to copy to the same final cell, but can change. For example, cells
A1:A3 contain "A1:small, A2:medium and A3:large". Clicking on one of these
pastes it into another cell, such as D1. Each time a different cell is
clicked in A1:A3, the text in D1 changes accordingly.

Any help is greatly appreciated.

I have a workbook that has many sheets, the first sheet has a lot of data on it that I want to have in a SUMPRODUCT formula, and I want to be able to copy that formula into multiple cells of the other sheets without changing the range the formula refers to. Example:
=SUMPRODUCT(--('All History'!$B2:$B2000=1),--('All History'!$C2:$C2000=2))

This formula is in one cell, but I want to have it copied to multiple others. But, when I try to copy it, the range changes, i.e. $B2 will become $B3 and so on.
I also need to be able to change the value I'm looking for without changing the rest of the formula, i.e. change the =1 value to =2, and so on. Any ideas?

I'm trying figure out how to copy text from a "list" (i.e., 4 or 5 text
entries in one column, but each in individual cells) to another cell in the
same spreadsheet by clicking on the original cell just once. The text always
needs to copy to the same final cell, but can change. For example, cells
A1:A3 contain "A1:small, A2:medium and A3:large". Clicking on one of these
pastes it into another cell, such as D1. Each time a different cell is
clicked in A1:A3, the text in D1 changes accordingly.

Any help is greatly appreciated.

Hi all

I'm new to VBA, but have recently found it solved many simple problems for me...

Now I'm stuck, I want to use VBA to copy the last entered cell in a column into the next empty row below, the last entered cell is a formula. I can successfuly run a macro where it selects both cells, and I've tried autofilling and copying - but I get the error message:

Run-time error '1004':

Method 'Range' of object'_Global' failed

My code is:
Sub FirstEmptyCell()

Dim FillDestination As Range
Dim FillFrom As Range

Set FillDestination = Range("A" & Rows.Count).End(xlUp).Offset(1)

Set FillFrom = Range("A65536").End(xlUp)

End Sub
Bear in mind my knowledge is very basic, I appreciate any help offered.

I am working with a table in Excel 2000.

Problem: I am trying to enter a formula into a cell, however, what
shows up is the actual formula instead of the results. What could be
"running" in the background to prevent the calculation? The Calcuation
in Options>Calculations is set to automatic.

Kathy L
Los Angeles, CA

Does anyone know if there is a way to do the following using VBA code.

If a number is entered into a range...say 'A1:A20' then VBA automatically copies a formula from cell 'C1' into every cell in column 'C' that has data entered in it in the range 'A1:A20. I need this to work in several columns, so columns 'D1' and 'E1' will have formmulas which need to be copied as well is data is entered into the 'A1:A20' range.

Any ideas,

many thanks

Basically I have 2 columns, one with dates, the other with 'open' or 'closed'. What I need to do is pick up the latest date in the column but ONLY for the open items.

So I have had to make a dummy column with the formula =IF(AND(S13="Open",ISNUMBER(R13)),R13,"")

This basically copies the date into this column if it is an open item and i can then use MAX(T13:T5000) to give me the latest date.

The problem I have is that when people add new rows, the formula to copy the date over for new items does not get put into the new rows. So the only thing I can think of as a solution is before the file is saved, copy the formulas in cells T13 and U13 right down to every cell until the last used row in the sheet. This way whenever the file is saved I will have every row with the correct formulas in it.

The other thing is the list may be filtered so when it copies the formulas down, it will have to copy it into every cell, not just the visible ones.

Anyone help with this? It would help with a whole load of my sheets which have the same problem of formulas needing to be copied down when the file is saved to ensure each row has the formula.

I guess I can put the code into the Workbook_BeforeSave procedure under ThisWorkbook, just unsure of the code to copy the formulas from T13 and U13 as into every cell down to the last used row.


I would like to copy a value from a cell in one sheet to another cell in different sheet. Before copying I would like to test if the value is greater than or smaller than a user specified value. If it is out of that range then quit otherwise loop through certain same steps.

For example on the attached file, I would like to fist test if date and time in cell K3 and L3 of sheet "HIST_EUR_USD" is less than date/time in cells N3 and O3 on sheet "Data1" and if true then copy cell A3 from "HIST_EUR_USD" sheet to Cell O107 in "Historical Data" sheet.

All three sheets are dynamic with data being updated.

I am hoping someone have some existing VBA routine that I can insert rest of my routine.

Thank you,

Hi, for some strange reason - while trying to copy formulas (as I had done before using the copy on a cell with a formula, and then paste special formula), it no longer offers any other option other than "Keep Text Only".

Anybody know what I have to change in excel - and where - so that I can get that function back, please?

Thanks and regards,


Here's my issue:

I have one sheet that will have data entered; for example, a staff member's name, a specific date, etc. Along with lots of other data.

On a second sheet (separated by a tab - within the same workbook), I need that data to carry over automatically. So, say, if a staff member's name is entered into a cell in sheet one, I need that to show up in sheet two automatically; the date entered in sheet one should also carry over. Basically so the rows more or less match, which sheet two will have the same info with totals.

So the question is - how do I get this info to carry over from one sheet to another? I've tried a few different formulas: just using an = sign and then clicking over to the other sheet and the other cell, but it doesn't work, it just gives me something like "=Table1[[#This Row],[Technician]]". I've tried using the =VALUE and =TEXT. I assume I'm missing something; I just need the name of the staff, the date, etc. to move over from one sheet to the next exactly as entered. How do I do this? Please help!

I use ctrl C and ctrl V to copy a formula into another cell and it won't work. Nor does my husband's sw work. What happens is the absolute value is copied. I read the help items which describe a dropdown "paste" menu that includes "F" but it doesn't show up in My Excel nor can I find it by going to "ribbon" commands. can you help?

Hi, I am a complete novice at VBA, but would like to make life abit easy, if possible. I need help with a code to look up if a cell value in is "Act", then copy formula form another cell in the same column and copy from row 6 to the last row in that column. The loop would be to then check for "Act" in the same row in the next column and copy paste formula from row 6 to the last row for that column.

Attachment 36823
The formula to be copied is looking up the data from a pivot table. I have been able to record a macro to lookup row 4 in column I for "Act" and then copy the forluma from row 2 from row 6 and autofill down to last row, however, do not know how to loop this formula for each column and write a vba code.

Any help appreciated.

I know of 2 ways to copy a formula down a column that I have listed below. But what about copying a formula down several thousand rows? It seems that there is a quick way to do this, but I cannot remember it. Instead drag the fill handle for many minutes. Can anyone tell me? Is it something in Go To... (I use Excel 2003.) Thanks in advance.

Copy and Paste
1. On the Edit menu, click Copy.
2. Select the cell you want to copy it to.
3. To copy the formula and any formatting, on the Edit menu, click Paste.
4. To copy the formula only, on the Edit menu, click Paste Special, and then click Formulas.

Drag the Fill Handle
1. Copy formulas into adjacent cells by using the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) .
2. Select the cell that contains the formula, and then drag the fill handle over the range you want to fill.

I am trying to use a combo box to select a text item from the list, once I
select this text item, I want it to place a $0 into another cell.

example: I select from combo box - Teriyaki Chicken, then I want $7.00 to
appear in cell C8 which will in turn be used to calculate a total based on
several other cells.

I just need to know how to take the text from the combo box in cell A8 and
place a dollar figure $7.00 in cell C8.

I am having trouble with creating a formula that locates a word that is entered in sheet1, it then finds that word on sheet2, then moves across 3 cells and down 4 cells in sheet2, and then prints the number found in that cell back into another cell in sheet 1. I hope that makes sense. Hopefully someone out there can help.


I have a very large spreadsheet with multiple work books, i have protected certain cells in each worksheet, eg Column S calculates values from Q and R. So therefore i do not want users to be able to change my formula in S. I have created a macro to allow the users to insert rows into each active worksheet but each new row that is inserted does not contain any formula, i therefore need to come up with some code that takes formula from column S and inserts formula into new cell in column S! Only problem is the user can enter a number of new rows in each worksheet!

Any help out there? i would be vrey grateful for any ideas

I have data on the clipboard which I want to paste into multiple cells in a
column that is selected. When I Ctrl+V, it only pastes into the first cell.
How can I get it to paste into all the cells that I have selected?

Thanks . . . Walt

What I want to do is :
to put a Formula into a Cell in running time

'KK is the index from the Sheet1
'FacInd other Sheet
'Sheet2 where the Cell Formula I want to put

KK = i + 2
Worksheets("Sheet2").Cells(iRow, 5).Formula = "=(Sheet1!$(KK,4) * FacInd!$E47
* FacInd!$E93)"
but this part (Sheet1!$(KK,4) does not works
I dont know if the syntaxes is correct? Because send a error (application-
defined or object defined error ) some thing like that.
also I have tried with a ListBox :
Worksheets("Sheet2").Cells(iRow, 5).Formula = "=(Sheet1!$(KK,4) * FacInd!$E47
* FacInd!$E93)"

just to mention as value I can get into the cell but as Formula I can not

Please I need Help to solve this


Message posted via

I am trying to put the value of one worksheet into another cell in another

No luck finding an answer? You could always try Google.