title: VBA Excel return value from other sheet
body: <p>please look at this picture</p>
  
  <p><a href="https://i.stack.imgur.com/iKToo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iKToo.png" alt="enter image description here"></a></p>
  
  <p><br>
  for that thing I use VBA like this</p>
  
  <pre><code>Sub TransformTbl()
     Dim i As Long, j As Long, cnt As Long
  
     With ActiveSheet
        .Range("G1:I1") = Array("Date", "Event", "Place")
        cnt = 1
        For j = 2 To 4     
           For i = 2 To 5   
              If Len(.Cells(i, j)) &lt;&gt; 0 Then
                 cnt = cnt + 1
                 .Cells(cnt, 7) = .Cells(1, j)  
                 .Cells(cnt, 8) = .Cells(i, j)  
                 .Cells(cnt, 9) = .Cells(i, 1)  
              End If
           Next i
        Next j
     End With
  End Sub
  </code></pre>
  
  <p>But it work in the same sheet. How can i make the table in the right in the sheet B? but the source (left table) in sheet A?</p>