Code to search job id from multiple sources and update main spreadsheet.

[kevins1218]

First time poster. Please bear with my ignorance of VBA. Also sorry for the long post. I have tried to come up with something and I am getting stuck on the searching part. I did write some code but I'm only at the beginning stage where I can open the workbook and pull data. Please help!

I need to do the following:
1> Pull data from multiple (2 in this case could be more) workbooks - these workbooks have information but not in the same order as the main sheet
1b> Open the source workbook (source1)
2> Search the main sheet to see if the job id exists
2a> If the job id exists, update the values in the row
3> If the job id does not exist, then create a new job row at the bottom and input data from the source
4> Close the workbook (source1)
5> Open the source workbook (source2)
6> Search the main sheet to see if the job id exists
6b> If the job id exists, update the values in the row
7> If the job id does not exits, then create a new job row at the bottom and input data from the source
8> Close the workbook (source2)
9> Sort the main sheet by date submitted column from earliest to oldest.

The following is the code I have so far:

Sub ImportDataFromSource()
Dim wb As Workbook
Dim wb2 As Workbook
Dim nameandpathsource1 As Variant
Dim nameandpathsource2 As Variant

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws3 As Worksheet, ws4 As Worksheet
Dim lastRow1 As Long, lastRow2 As Long
Dim idCol1 As Range, idCol2 As Range
Dim searchID As Variant, foundCell As Range
Dim rf


Application.DisplayAlerts = False

'selecting what the source1 file is (source1)
nameandpathsource1 = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened as Source1")
If nameandpathsource1 = False Then Exit Sub

'1st source
Workbooks.Open Filename:=nameandpathsource1
Set wb = Workbooks.Open(nameandpathsource1)

'add's a new sheet to the main page and name this sheet as source 1
Sheets.Add
ActiveSheet.Name = "Source1"
wb.Sheets("Sheet1").Range("A1:K16").Copy '<--NEED A METHOD TO COPY ALL DATA FROM THE SHEET, COULD BE MORE THAN THIS RANGE!
ThisWorkbook.Sheets("Source1").Range("A1").PasteSpecial xlAll

'close source1
wb.Close False

'make main page the active sheet.
Worksheets("Main").Activate


'Search each id on show page and add in data or create a new entry - HAVING ISSUES FROM HERE ON!
' Set references to the worksheets
Set ws1 = ThisWorkbook.Sheets("Source1")
Set ws2 = ThisWorkbook.Sheets("Main")

' Assuming your IDs are in column A
lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

' Assuming your IDs start from row 2
Set idCol1 = ws1.Range("A2:A" & lastRow1)
Set idCol2 = ws2.Range("A2:A" & lastRow2)

' Loop through each ID in Source1
For Each searchID In idCol1
' Search for the ID in Main
Set foundCell = idCol2.Find(searchID.Value, LookIn:=xlValues)

If Not foundCell Is Nothing Then

' ID exists in Sheet2, update values in Main - THIS IS WHERE MY BRAIN TURNED OFF!




Else
' ID doesn't exist in Sheet2, create a new row in Sheet2 - ALSO THIS IS MESSED UP!
lastRow2 = lastRow2 + 1
ws2.Cells(lastRow2, 1).Resize(1, 4).Value = searchID.Offset(0, -1).Resize(1, 4).Value
ws2.Cells(lastRow2, 2).Value = searchID.Value
End If
Next searchID

Worksheets("Source1").Delete






'selecting what the source file is (source2)
nameandpathsource2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened as Source2")
If nameandpathsource2 = False Then Exit Sub


'2nd source
Workbooks.Open Filename:=nameandpathsource2
Set wb2 = Workbooks.Open(nameandpathsource2)


'name this sheet as source2
Sheets.Add
ActiveSheet.Name = "Source2"
ThisWorkbook.Sheets("Source2").Range("A2").PasteSpecial xlAll

'close source2
wb2.Close False

'Search each id on show page and add in data or create a new entry

' Set references to the worksheets
Set ws3 = ThisWorkbook.Sheets("Source2")
Set ws4 = ThisWorkbook.Sheets("Main")

' Assuming your IDs are in column A
lastRow1 = ws3.Cells(ws1.Rows.Count, "A").End(xlUp).Row
lastRow2 = ws4.Cells(ws2.Rows.Count, "A").End(xlUp).Row

' Assuming your IDs start from row 2
Set idCol1 = ws3.Range("A2:A" & lastRow1)
Set idCol2 = ws4.Range("A2:A" & lastRow2)

' Loop through each ID in Source1
For Each searchID In idCol1
' Search for the ID in Main
Set foundCell = idCol2.Find(searchID.Value, LookIn:=xlValues)

If Not foundCell Is Nothing Then

' ID exists in Sheet2, update values in Main - THIS IS WHERE MY BRAIN TURNED OFF!




Else
' ID doesn't exist in Sheet2, create a new row in Sheet2 - ALSO THIS IS MESSED UP!
lastRow2 = lastRow2 + 1
ws4.Cells(lastRow2, 1).Resize(1, 4).Value = searchID.Offset(0, -1).Resize(1, 4).Value
ws4.Cells(lastRow2, 2).Value = searchID.Value
End If
Next searchID

Worksheets("Source2").Delete

Application.DisplayAlerts = True

End Sub

 

[mumps]

You are very welcome.