Hello all,

I've copied a module I found on ExtendOffice and modified it to suit my needs. When I click a button, it pulls all unique Employee IDs from column C of data exported from FileMaker (RawData!). It pastes these unique values in column A of a table on a different sheet (MainTool!).

The table in the main tool then uses the Employee IDs to cross reference the RawData for more information using an index match.

While the code works great and does exactly what I want it to, it also deletes all values and formulas (including all my index match stuff) in columns F onward.

I'd like it to only replace the data in column A without deleting anything. 


Private Sub CommandButton2_Click() '<<=====Populate Button
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.Range(Cell1:="RawData!$C2:$C3000")
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("A8")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("A8:A" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "RawData!C").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("A8:A" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("A8:A" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub
If there's no values in column A before pressing the button, the module works fine. But if I need to update the values already in the Main Tool and press the button again, here's what happens (Employee names & table headers erased):

sample.JPG

I think it erases all of my formulas because it's deleting duplicates, but even changing where it copies the range (xRng.Copy Range("A8")) to a cell below the original unique values (I have 97 unique values; I changed Range("A8") to Range("A105")) in the hopes of having the macro delete duplicates below the ones connected to formulas did not work.

Any help is greatly appreciated.