CODEWITHSUNDEEP
excelvba
user3503531
user3503531

Reputation: 1

Mirror Cell Values in Excel Using VBA

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

Upvotes: 0

Views: 1489

Answers (1)

user3514930
user3514930

Reputation: 1717

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

Upvotes: 0

Related Questions