Merge Data From one Workbook to another
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
|