связывание столбца и строки

Option Explicit
Private Sub Worksheet_Activate()
     Dim a As Range
     Set a = ActiveSheet.Range("G:G")     '// 6th column in the source sheet
     a.Activate
     Dim cel As Range
     Set cel = a.Cells.SpecialCells(xlCellTypeBlanks)
     '// MsgBox "Count cells: " & cel.Row '// for debug only
     Dim b As Range
     Set b = a.Range(Cells(1, 1), Cells(cel.Row - 1, 1))
     b.Activate
     b.Select     'for visualization only
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Columns.Count = 1 Or _
        Target.Rows.Count = 1 Or _
        Target.Column = 7 Then     '// 7 is column number in the source sheet
          Target.Activate
          Dim ns As Worksheet
          Set ns = Sheets(2)     '// sheet number
          Dim nr As Range
          Set nr = ns.Rows(2)     '// row number in the next sheet
               Dim v
               v = Target.Value
               nr.Cells(1, Target.Row).Value = v
     Else
          Exit Sub
     End If
End Sub