title: Excel VBA to move data from table to table based on column name
body: <p>I created a procedure that loops in all header columns of table 1 and search for the same header column in table 2 to update relveant cells based on matched column name.</p>
  
  <p>This is the code for this procedure:</p>
  
  <pre><code>Do While i &lt;= SourceTableColumnCount
  ColumnName = sourceTable.HeaderRowRange(i).Value
                      On Error Resume Next
                      DestColumnIndex = destTable.Range.Find(ColumnName, MatchCase:= True, SearchFormat:=False, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookAt:=xlWhole).Count
                          If Err.Number &lt;&gt; 0 Then
                              'In case column name in source table is not found in destination table
                          Else
                              destTable.DataBodyRange(DestRowIndex + 1, DestColumnIndex).Value =  sourceTable.DataBodyRange(r + 1, i).Value
                          End If
                      i = i + 1
  Loop
  </code></pre>
  
  <p>The problem is DestColumnIndex is always 1. Although column name in destination table is matching column name in source table. It should work like this:</p>
  
  <p>1- Go to first column in table 1 and get it's value
  2- Go to table 2 and search for value from step 1
  3- For the found result, get the range.column (Index of this column)
  4- Set the cell value where range (Row, DestColumnIndex)</p>
  
  <p>my problem is in step 3 where range.column is always 1 so the output of step 4 is always updating cell where column index is 1.</p>
  
  <p>I hope the problem is clear.</p>