Mirror Cell Values in Excel Using VBA

Question

I am trying to the value of some cells using the code below but it is coming up with an error (Method 'Range' of object'Worksheet' failed) on one line(Worksheets(wsName).Range). I imagine that it is because i am referencing data with the same worksheet but i cannot find what to put instead of worksheets. Also it does not update if the value in cell D5 changes it just stays what it originally was in F5. Can anyone help me out with this?

Public Sub Worksheet_Change(ByVal Target As Range)
   MirrorCells
End Sub
Public Sub MirrorCells()
   Dim DestinationWS As Variant
   DestinationWS = Array("Stock Levels") 
   Dim wsName As Variant

   For Each wsName In DestinationWS
      Worksheets(wsName).Range("F5") = Range("D5")
   Next wsName
End Sub

Have u tried Worksheets(wsName).Range("F5").value = Range("D5").value — Sathish Kothandam

user3514930 user3514930 · Accepted Answer · 2014-04-11T11:10:09

You are in a loop. You continue to update the cell...

  If (Worksheets(wsName).Range("F5").Value <> Range("D5").Value) Then
    Worksheets(wsName).Range("F5").value = Range("D5").value
  End If

Mirror Cell Values in Excel Using VBA

1 Answers