Need VBA suggestions to import and update excel data

[SchellElec]

We keep track of our job history in an excel file, using data that we export from Quickbooks. I'm given a file weekly to append new rows and update existing rows, and we want to keep all the data, even if it is no longer on the weekly update file.

I'd like to know if a macro can achieve the following:
Run a macro from the summary file.
Select the weekly update file to be used.

Set WB2 = Workbooks.Open(Application.GetOpenFilename(Title:="Select the new file with the new info", MultiSelect:=False))

seems to work well.
For each row in the update file, search if the job already exists in summary file. If so, update that row. If not, add row to bottom.
When finished, sort the sheet so that jobs are in alphanumeric order.

For example, the summary file is formatted like this:

Job#	Receipts	COGS	Gross Profit	Total Expense	Net Income
J3662-05	5211.00	5021.36	189.64	88.97	100.67
J3663-27C	602.64	280.77	321.87	22.77	299.10
J3708-11C	783.20	500.82	282.38	48.96	233.42

A weekly update file may look like this:

Job#	Receipt	COGS	Gross Profit	Total Expense	Net Income
J3663-27C	602.64	315.36	287.28	38.77	248.51
J3668-17C	495.80	312.14	183.66	12.67	170.99
J3709-15C	1199.20	737.00	462.20	39.76	422.44

And my desired output for the summary file would look like this:

Job#	Receipts	COGS	Gross Profit	Total Expense	Net Income
J3662-05	5211.00	5021.36	189.64	88.97	100.67
J3663-27C	602.64	315.36	287.28	38.77	248.51
J3668-17C	495.80	312.14	183.66	12.67	170.99
J3708-11C	783.20	500.82	282.38	48.96	233.42
J3709-15C	1199.20	737.00	462.20	39.76	422.44

 

[alansidman]

The easiest way to do this may not be VBA but Power Query. Look at this video on how to set it up.

 

[Akuini]

@SchellElec. welcome to MrExcel.
Try this code:
You need to amend the sheet reference to suit.

Sub SchellElec2()
Dim c As Range, a As Range, b As Range
Dim n As Long
Dim wb1 As Workbook, wb2 As Workbook
Application.ScreenUpdating = False


Set wb1 = ThisWorkbook
With wb1.Sheets("Sheet1")  'change to suit
    n = .Range("A" & Rows.Count).End(xlUp).Row
Set a = .Range("A1:A" & n)
End With


Set wb2 = Workbooks.Open(Application.GetOpenFilename(Title:="Select the new file with the new info", MultiSelect:=False))

With wb2.Sheets("Sheet1") ''change to suit
    Set f = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With a
    For Each c In f
        res = Application.Match(c.Value, a, 0)
            If IsNumeric(res) Then
                .Cells(res, 1).Resize(, 6).Value = c.Resize(, 6).Value
            Else
                n = n + 1
                .Cells(n).Resize(, 6).Value = c.Resize(, 6).Value
            End If
    Next
End With

Set a = a.Resize(n, 6)
With a
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
End With
Application.ScreenUpdating = True

End Sub

 

[SchellElec]

@SchellElec. welcome to MrExcel.
Try this code:
You need to amend the sheet reference to suit.

Sub SchellElec2()
Dim c As Range, a As Range, b As Range
Dim n As Long
Dim wb1 As Workbook, wb2 As Workbook
Application.ScreenUpdating = False


Set wb1 = ThisWorkbook
With wb1.Sheets("Sheet1")  'change to suit
    n = .Range("A" & Rows.Count).End(xlUp).Row
Set a = .Range("A1:A" & n)
End With


Set wb2 = Workbooks.Open(Application.GetOpenFilename(Title:="Select the new file with the new info", MultiSelect:=False))

With wb2.Sheets("Sheet1") ''change to suit
    Set f = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With a
    For Each c In f
        res = Application.Match(c.Value, a, 0)
            If IsNumeric(res) Then
                .Cells(res, 1).Resize(, 6).Value = c.Resize(, 6).Value
            Else
                n = n + 1
                .Cells(n).Resize(, 6).Value = c.Resize(, 6).Value
            End If
    Next
End With

Set a = a.Resize(n, 6)
With a
    .Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
End With
Application.ScreenUpdating = True

End Sub

@Akuini This works beautifully. Thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.