Search code examples
excelvbaformulacell

VBA "=active cells" from another work sheet

I am trying to use VBA to update a cell in a new worksheet to equal the active cells from a schedule on another sheet.

I have tried many variants of the below code, but at present, I have only managed to copy the cell values across. I am seeking to update the cells in the new sheet to equal the original cells as they would if you typed for example " =SheetX!A1 " directly into the cell.

Worksheets("Template").Visible = True
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "NewName"

ThisWorkbook.Worksheets("Tech_Sub_Reg").Activate


Dim R As Range
Dim S As Range
Dim I As Range
Dim B As Range

Set R = ActiveCell
Set S = ActiveCell.Offset(0, 1)
Set I = ActiveCell.Offset(0, 2)
Set B = ActiveCell.Offset(0, 3)

Sheets("NewName").Range("A125").Formula = R
Sheets("NewName").Range("B125").Formula = S
Sheets("NewName").Range("C125").Formula = I
Sheets("NewName").Range("D125").Formula = B
Solution

  • Change e.g.

    Sheets("NewName").Range("A125").Formula = R

    to

    Sheets("NewName").Range("A125").Formula = "=" & R.Address(External:=True)

    If you also want to remove the workbook-name from the formula, replace what comes after "=" & in the line above with

    Replace(R.Address(External:=True), "[" & R.Parent.Parent.Name & "]", "", 1, -1, vbTextCompare)