Macro to copy formula while keeping original cell references

[excel_monkey]

Hi,

I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.

E.g.

If the formula on Sheet1 is:

= sum(A1:B6)

then the copied formula on Sheet2 would read

=sum(Sheet1!A1:Sheet1!B6)

You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.

I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.

Thanks in advance.

 

[ajm]

use absolute references in your formula.

try

Worksheets("Sheet2").Range("A1").Formula = "=sum(Sheet1!$A$1:Sheet1!$b$6)"

is that what you need?

 

[excel_monkey]

Hi,

Thanks for the reply. That's not what I need.

I need the macro to work however the original cell is written, and I don't want to change the original cell.

I don't want to change the original cell in any way. This is because i don't want excel to have to recalculate the original cell.

The best I have is the following:

temp_formula = Application.ConvertFormula(Formula:=ActiveCell.Formula, fromreferencestyle:=Application.ReferenceStyle, toReferenceStyle:=xlA1, toabsolute:=xlAbsolute)

ActiveWorkbook.ActiveSheet.Range("IV65536").Formula = temp_formula
ActiveWorkbook.ActiveSheet.Range("IV65536").Cut (worksheets(2).Range("A1"))

This makes an absolute version of the formula without changing the cell and then pastes it to a (hopefully) unused cell, and then cuts that cell, and pastes it to another sheet.

What I want to do is avoid the step of pasting the converted formula into cell "IV65536"

Does that make sense?

Thanks for your help.

 

[ajm]

sorry mate. I have tried everything i can think of this morning and nada! if the code you have below works, are you able to run with it for now and keep searching? I use "helper cells" (IV65536 in your example) in most of my stuff as I find it easier to see whats going on. just a thought though, why not move your helper cell closer to the action so that your used range isn't the entire sheet. alternatively, set print area to ensure you don't kill too many trees if you have to print it out for any reason.

once again, my apologies. i'll keep at it from here and see what I can find also.

 

[GTO]

E.g.

If the formula on Sheet1 is:

= sum(A1:B6)

then the copied formula on Sheet2 would read

=sum(Sheet1!A1:Sheet1!B6)

Greetings excel_monkey,

Probably just a typo, but the formula on the new sheet would be:
=SUM(Sheet1!A1:B6)

Not sure I read your question correctly, but if so, you are wanting to "copy" the formula over to another sheet (via vba) and copying fails to insert the "Sheet1!" part. Is that the issue?

If so, as you did not mention what cell the formula is in, or what cell its supposed to be copied to on Sheet2... in this example, the formula is in Cell D1 on Sheet1 and we want it in the same cell on Sheet2.

Sub Formula_Import()
    
    With Worksheets("Sheet1")
        If .Range("D1").HasFormula Then
            Worksheets("Sheet2").Range("D1").Formula = _
                Left(.Range("D1").Formula, InStr(1, .Range("D1").Formula, "(", _
                     vbTextCompare) _
                     ) & _
                .Name & "!" & _
                Right(.Range("D1").Formula, Len(.Range("D1").Formula) - _
                      InStr(1, .Range("D1").Formula, "(", vbTextCompare) _
                     )
        End If
    End With
End Sub

Does that help?

Mark

 

[excel_monkey]

Thanks Mark.

The sub you put together works, but only if the formula contains only one reference.

For example:
Sum(A1:B2) converts to Sum(Sheet1!A1:B2)
Sum(A1:B2,C3) converts to Sum(Sheet1!A1:B2,C3)
i.e. no "Sheet1!" in front of C3

Using instr() would get too complicated, I think, if the formula to be copied was too long.

So far the best I've come up with is the following:

Sub copy_formula()

Sheets(1).Range("A1").Cut (Sheets(2).Range("A1"))
formula_string = Sheets(2).Range("A1").Formula
Sheets(2).Range("A1").Cut (Sheets(1).Range("A1"))
Sheets(2).Range("A1").Formula = formula_string

End Sub

This sub copies a formula from A1 of sheet1 to A1 of sheet2, whilst keeping the original sheet references. The problem is that doing this causes excel to recalculate cell Sheet1!A1, even though it is unchanged.

The idea behind this sub is that it will be used to copy the active cell to a set cell in another sheet. This is particularly usefull when constructing some spreadsheets. The reason that I don't want excel to recalculate the original cell is that it may take a long time to do that if the spreadsheet is complicated.

I have not included ActiveCell stuff in the code to keep the problem simple.

Thanks again for your help guys.

 

[excel_monkey]

I've come up with a better (half) solution now.

It copies the formula of the active cell to a helper cell using activecell.formula, and then cuts the helper cell to the other worksheet, thus preserving all the original sheet and workbook references.

sub copy_formula()
 
formula_string = ActiveCell.Formula
ActiveWorkbook.ActiveSheet.Range("IV65536").Formula = formula_string
ActiveWorkbook.ActiveSheet.Range("IV65536").Cut (dest_sheet.Range("A1"))
dest_sheet.Range("A1").Select
 
end sub

This leaves the active cell unchanged (so doesn't need to be recalculated) and copies its formula, via a helper cell (I use "IV65536") to cell "A1" in the destination worksheet (dest_sheet).

Ideally I would like to be able to do this without having to use the helper cell at all, and without disturbing the original cell, but I can't figure out how to do that!

 

[excel_monkey]

I guess what I'm trying to do is to cut a cell from one sheet to another without deleting/changing the original cell.

 

[Norie]

Why not just put a formula in the other sheet that refers to the cell containing the formula on the first sheet?

 

[mikerickson]

I put this in a cell on sheet 1, =SUM(Sheet1!$A$1:$A$5)

After copying it, and pasting it into a cell in sheet2 and the destination cell held =SUM(Sheet1!$A$1:$A$5)


The formula =SUM(Sheet1!A2:A6) in sheet1! B1, became =SUM(Sheet1!A2:A6) when copy pasted into sheet2! B1