dbc23, please educate people you are requesting help from that you are cross posting.
Anyway, I put this together and tested it and it should work. There are definately more eloquent ways of doing this, but it should be fool-proof. I also did you a favor and made it universal, so you dont need multiple macros for each state (it should work regardless of the state - so you can copy paste to your other masters).
Let me know if you run into any issues!
NOTE: I also added a functionality which should prompt you to pick the .csv file, allowing it to be universal and removing any issues you had with workbook variable assignment. You need microsoft scripting runtime for this functionality. Reference this:
- To reference this file, load the Visual Basic Editor (ALT+F11)
- Select Tools > References from the drop-down menu
- A listbox of available references will be displayed
- Tick the check-box next to 'Microsoft Scripting Runtime'
Sub UpdateDataWithNew()
Dim mainWorkbook As Workbook
Dim newWorkbook As Workbook
Dim mainWorkbookDataSheet As Worksheet
Dim csvWorkbookDataSheet As Worksheet
Dim OldRange As Integer
Dim NewRange As Integer
Dim ListOld(), ListNew(), csvMovingData(), xlsmMovingData()
Dim xlFile As Variant
Dim fso As New FileSystemObject
' Set workbook/worksheet variables
Set mainWorkbook = ThisWorkbook
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)
xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select .csv File for Evaluation", "Open", False)
xlFileName = fso.GetFileName(xlFile) ' Gets filename of chosen file
Workbooks.Open xlFile ' Opens .mtwData Workbook
' Set workbook/worksheet variables
Set newWorkbook = Workbooks(xlFileName)
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)
xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 1 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With mainWorkbookDataSheet
ListOld = Range(.Cells(xlsmFirstRow, 2), .Cells(xlsmLastRow, 2)).Value
End With
With csvWorkbookDataSheet
ListNew = Range(.Cells(csvFirstRow, 2), .Cells(csvLastRow, 2)).Value
End With
Index = 0
Counter = 1
Dim x As Range, rowIdx As Long
For i = LBound(ListNew) To UBound(ListNew)
Found = False
For j = LBound(ListOld) To UBound(ListOld)
If ListNew(i, 1) = ListOld(j, 1) Then
Set x = mainWorkbookDataSheet.Cells.Find(What:=ListNew(i, 1)) ' Finds row in xlsm workbook for the value matched from csv workbook
rowIdx = x.Row ' Gets row index value
mainWorkbookDataSheet.Range("O" & rowIdx & ":W" & rowIdx).Value = csvWorkbookDataSheet.Range(Cells(i, 15), Cells(i, 23)).Value ' Sets xlsm row values (Column O:W) to csv values
Found = True
End If
Next j
If Found = False Then
mainWorkbookDataSheet.Range("A" & xlsmLastRow + Counter & ":W" & xlsmLastRow + Counter).Value = csvWorkbookDataSheet.Range(Cells(i, 1), Cells(i, 23)).Value
Counter = Counter + 1
End If
Next i
newWorkbook.Close
End Sub