Changing column reference in a copied worksheet

[DRFexcel]

Hey guys, new here so appreciate any help.

I have worksheet A with 72 formulas that all reference the same column (but several different rows) in a different worksheet B.

Is there a way to copy worksheet A and then quickly change all the formulas so they refer to a different column in worksheet B?

To clarify--
Worksheet A's formulas all refer to cells in column C of Worksheet B. I'd like to create a copy of Worksheet A and then change all 72 formulas to refer to cells in column D of Worksheet B (same rows).

Is this possible without having to go formula by formula and manually changing the "Cs" to "Ds"?

Thanks for any tips!

 

[mumps]

Try:

Sub ChangeFormulas()
    Application.ScreenUpdating = False
    Dim rng As Range
    Sheets("A").Copy after:=Sheets(Sheets.Count)
    For Each rng In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        rng.Formula = Replace(rng.Formula, "C", "D")
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[SawdustCollector]

You could also try find and replace (edit: ctrl = F). No VBA needed.
Since your reference is in a different workbook, you will have a "!" preceding your "C", which will make it possible to replace all without replacing the "C"s in other places throughout the workbook. Be careful to check that it didn't replace any C's you wanted left alone though.

 

[DRFexcel]

Ah, great idea! I just used find and replace to do one sheet, but had to skip a few formulas that contained other instances of C that I didn't want to change (and then go back and do those manually). I had not thought to combine the ! with the C.

I will try that next and see how smoothly that works, thank you!

 

[DRFexcel]

That worked brilliantly!