copy value from another sheet when making a new one, and keep the reference to that cell

[nemke]

I would like to copy the value from a cell in the existing sheet to a cell in a newly created sheet using VBA. Also, every time the value from the original cell changes, immediately to update the value in the newly created sheet. The sheet name is dynamically set, and when trying to reference it via variable, I get the dialog box for updating values, saying the sheet does not exist

For example:

Sheets(some variable).Range("A1") is always referenced to Sheets(some variable).Range("A1")

Thanks in advance

 

[rlv01]

Maybe this?

Sub SimpleExample()
    Dim srcWS As Worksheet, destWS As Worksheet       'Declare worksheet variables
    Dim srcRange As Range, destRange As Range         'Declare range variables

    'Define existing sheet as source worksheet
    Set srcWS = ActiveWorkbook.Worksheets("Sheet1")

    'Create a new worksheet as destination worksheet
    Set destWS = ActiveWorkbook.Worksheets.Add

    'Set source and desitination cells
    Set srcRange = srcWS.Range("A1")
    Set destRange = destWS.Range("A1")

    'Set destination cell formula
    destRange.Formula = "=" & srcRange.Address(, , , 1)
End Sub

 

[nemke]

Greeeeeeat, I just change the corresponding values and it worked. Thank you very much!

Can you tell what why the dialog for updating values appears? And what do those commas after "Address" in the brackets mean?

 

[rlv01]

Can you tell what why the dialog for updating values appears?

That does not happen for me, so I will need more information. What line of code produces the dialog, and what specifically does it say?

And what do those commas after "Address" in the brackets mean?

The address property has various parameters and I selected the one to produce an external reference so that the address points to a different sheet. I could have also written it as
destRange.Formula = "=" & srcRange.Address(External:=True)

Range.Address property (Excel)

Office VBA reference topic

learn.microsoft.com