title: Using cell value to specify the paste location in Excel VBA
body: <p>I have a table with two rows : the first row contains the locations where the value of the second row should be pasted.</p>
  
  <p>For example : </p>
  
  <pre><code>row 1 : sheet8!D2  sheet6!D2   sheet2!C5
  row 2 : apple      lemon       pEER
  </code></pre>
  
  <p>So apple should be pasted in sheet 8 cell D8. Lemon should be pasted in sheet6 cell D2. The problem is that the value apple is pasted everywhere (in <code>sheet8!D2</code>, <code>sheet6!D2</code> and <code>sheet2!C5</code>). How can I correct this?</p>
  
  <pre><code>Sub Sample()
  Dim rng As Range
  Dim Sh As String, Cl As String
  Dim ws As Worksheet
  Dim i As Integer
  Dim Row1 As String
  
  ncol = Range("A1:F1").Columns.Count
  
  For i = 1 To ncol
  
  
  Row1 = Range("A1:F1").Cells(1, i).Value
  
  Set ws = ThisWorkbook.Sheets("Sheet2")
  
  With ws
      Sh = Split(Row1, "!")(0)
      Cl = Split(Row1, "!")(1)
  
      Set rng = ThisWorkbook.Sheets(Sh).Range(Cl)
  
      rng.Value = .Range("A2").Value
  
  End With
  
  Next i
  
  End Sub
  </code></pre>