I have a database for employees training records and have built a macro to update the database as such (see Update sheet). The Update sheet queries the database for the employee name (F9) from the Database (column A) and paste adjacent values (G9:R9) into the database column H:S.
The current macro (SaveUpdate) does this perfectly only when the corresponding H:S is empty. When the columns are filled they overwrites the existing data (which I don't want that to happen). Instead, I would want the data to be pasted 1 row below the existing data (just like H9:S9). The question is how can I get this done? As the employees may or may not have existing training records and they may have a varying no. of existing records, my macro needs to be dynamic enough to achieve this.
Current code is:
Dim ws1 As Worksheet, ws2 As Worksheet, fAddress$
Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
Application.ScreenUpdating = False
Set ws1 = Sheets("Database")
Set ws2 = Sheets("Update")
Set LookInR = Range(ws1.Range("A1"), ws1.Range("A" & Columns.Count).End(xlUp))
Set LookForR = Range(ws2.Range("F9"), ws2.Range("F" & Rows.Count).End(xlUp))
For Each c In LookForR
With LookInR
Set FoundOne = .Find(What:=c, LookAt:=xlPart)
If Not FoundOne Is Nothing Then
Do
fAddress = FoundOne.Address
FoundOne.Offset(, 7).Value = c.Offset(, 1).Value
FoundOne.Offset(, 8).Value = c.Offset(, 2).Value
FoundOne.Offset(, 9).Value = c.Offset(, 3).Value
FoundOne.Offset(, 10).Value = c.Offset(, 4).Value
FoundOne.Offset(, 11).Value = c.Offset(, 5).Value
FoundOne.Offset(, 12).Value = c.Offset(, 6).Value
FoundOne.Offset(, 13).Value = c.Offset(, 7).Value
FoundOne.Offset(, 14).Value = c.Offset(, 8).Value
FoundOne.Offset(, 15).Value = c.Offset(, 9).Value
FoundOne.Offset(, 16).Value = c.Offset(, 10).Value
FoundOne.Offset(, 17).Value = c.Offset(, 11).Value
FoundOne.Offset(, 18).Value = c.Offset(, 12).Value
Set FoundOne = .FindNext(After:=FoundOne)
Loop While FoundOne.Address <> fAddress
End If
End With
Next c
Set ws1 = Nothing
Set ws2 = Nothing
Set LookInR = Nothing: Set LookForR = Nothing
Any help will be much appreciated.
Bookmarks