View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
RadarEye RadarEye is offline
external usenet poster
 
Posts: 78
Default 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