Merge Data From one Workbook to another

[Kenny]

I have two work books. One is "historical" data and the other is "update"
data for records in the first. I want to program some vb to update
"historical" with the "update" data. Also I would not be using all the data
in the whole row. I will only be updating certain columns of a row to
historical:

Column A in both is the account number. The script should take each account
number in "update" and match it to the account number in historical, if there
is a match it should update columns B, E, M, O of historical with update. If
there is no match it should add a new record and update the same columns.

Also this code should be contained in historical, also I need a way to
activate rows in historical to show active records, what I mean by active is
only records that were updated or added should be active. In other words
maybe add another column called active to historical and as the script runs
it puts the word active in a column or deactive in the same column and then I
could filter it to show only the active ones. Im not sure if there is a
better way of doing that or not, but i do not know how to code my thought
either. Thanks so much in advance.

 

[RadarEye]

Hi Kenny.

I have created a possible macro which might do th trick.
I advice to use a date in the extra column, this will give you to my
opinion more info
about the activety off each account.

' ------------ Begin of code ------------
Sub UpdateFromFile()
Dim wbkUpdate As Workbook
Dim shtUpdate As Worksheet
Dim strFilename As String
Dim lAccntNmbr As Long
Dim lCollB As Long
Dim lCollE As Long
Dim lCollM As Long
Dim lCollO As Long
Dim lRowUpd As Long
Dim lRowHis As Long
Dim blnUpdated As Boolean

Dim datUpdate As Date

datUpdate = Now

strFilename = Application.GetOpenFilename("Excel files
(*.xls),*.xls", _
, "Select update file")
If strFilename <> "" Then
ThisWorkbook.Sheets("AccountInfo").Select
Set wbkUpdate = Application.Workbooks.Add(strFilename)
Set shtUpdate = wbkUpdate.Sheets("AccountInfo")
lRowUpd = 2
Do
With shtUpdate
lAccntNmbr = .Cells(lRowUpd, 1).Value
lCollB = .Cells(lRowUpd, 2).Value
lCollE = .Cells(lRowUpd, 5).Value
lCollM = .Cells(lRowUpd, 13).Value
lCollO = .Cells(lRowUpd, 15).Value
End With
blnUpdated = False
With ThisWorkbook.ActiveSheet
lRowHis = 1
Do
lRowHis = lRowHis + 1
Loop Until .Cells(lRowHis, 1).Value = lAccntNmbr _
Or IsEmpty(.Cells(lRowHis, 1))
.Cells(lRowHis, 1) = lAccntNmbr
.Cells(lRowHis, 2) = lCollB
.Cells(lRowHis, 5) = lCollE
.Cells(lRowHis, 13) = lCollM
.Cells(lRowHis, 15) = lCollO
.Cells(lRowHis, 16) = datUpdate
End With
lRowUpd = lRowUpd + 1
Loop Until IsEmpty(shtUpdate.Cells(lRowUpd, 1))
wbkUpdate.Close SaveChanges:=False
End If
End Sub
' ------------ End of code ------------

Hope this helps

Radareye

 

[Kenny]

Thank you so much and I am sorry it took me a while to get back to you.

I am having som problems with this code. I am getting errors on these two
lines. What do they refer too what is "accountinfo"?

ThisWorkbook.Sheets("AccountInfo").Select
Set shtUpdate = wbkUpdate.Sheets("AccountInfo")

Also I wanted to make sure you understand what I am doing.
First, both of these sheets are exsisting. There is one that is the history
that is maintained by me, the other is update data or new accounts, the code
will be placed in the history, once ran it will extract update data from the
update file and update the account number and will add records that are not
already exsisting in the history file. You may understand this and I do not
understand what my workbooks should be named and my sheets should be named to
make your code work. let me know thanks so mcu sir!