Update table values from another Table
<p>I have a workbook with a table that is roughly 20000 rows in size and 52 columns. At times, I need to update a percentage of select rows at once. I'm hoping to use a macro to update the select cells based on a value in the row, mapped out by a second smaller table with the updated values to be entered in to table 1. Almost like a VLOOKUP function, but one that doesn't erase the cell if the entry isn't found. For example, change the Phone Number according to the Host ID.</p>

<p>I tried to do this with an Array in the code below for a specfic set of the values in Table 1, but my values didn't update. My VBA is a bit rusty, so if someone can review and assist with getting this to function, it would be appreciated. I would like to make it update any entry in the table based on the table headers eventually.</p>

<pre><code>Sub NewNameandCostCenter()
Dim myList, myRange
Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim LastRow2 As Long
Set sht = Worksheets("NewNameMacro")
Set sht2 = Worksheets("ALL")
Set StartCell = Range("A2")

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'set myList array
Set myList = sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
LastRow2 = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'set myRange array
Set myRange = Sheets("ALL").Range("J2:M" &amp; LastRow2)
'Update values of cells adjacent
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 2).Value, LookAt:=xlWhole
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 3).Value, LookAt:=xlWhole
Next cel
End Sub
</code></pre>

<p>Thanks,
JD</p>