Code to update staff hours from a rota to a wage tracker

[BondNo9]

<code>Hi all,


I have been trying to develop a macro that can match a staff name on a rota spreadsheet with another spreadsheet, and then copy across that staff members hours, it is to repeat this for 21 entries.

The below code is what i have come up with so far(it also does a couple of other functions such as clear off the previous weeks staff tabs and data unless they are to be carried over to the next week, this part works fine) however all it does is input the data into the first row(Row 9) again and again. I'm not entirely sure where i have gone wrong, i'm guessing my lookup function is wrong/inappropriate for the task.

Any advice would be much appreciated. I am very much an amateur at this so if you happen to see a more efficient way of doing the task that too would be amazing, as my code is a bit of a frankenstein of various websites code and tips.

I am using Excel 2007

Thanks folks for having a read.
</code>

Sub updatewages()
'

    Dim i As Integer
    ScreenUpdating = False
ActiveSheet.Unprotect
    Sheets("Sub & Wage Tracker").Select
    ActiveSheet.Unprotect
    Sheets("Current Rota").Select
    ActiveSheet.Unprotect
   Range("a4").Select
    
    For i = 1 To 21
    saddress = ActiveCell.Address
    Selection.Copy
    Sheets("Sub & Wage Tracker").Select
    Range("C4").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Current rota").Select
    Range(saddress).Select
    ActiveCell.Offset(0, 39).Select
    Selection.Copy
    Sheets("Sub & Wage Tracker").Select
  Range("c4").Select
  Range("a9").Activate
 ActiveCell.Value = "=vlookup(c4,a9:a30," & i & ",FALSE)"
    ActiveCell.Offset(0, 2).Select
     Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 6).Select
    If IsEmpty(ActiveCell) = True Then
    ActiveCell.Offset(0, -5).Select
    Selection.ClearContents
    End If
    Sheets("Current Rota").Select
    Range(saddress).Offset(1, 0).Select
        
    Next i
ActiveSheet.Protect
Sheets("Sub & wage Tracker").Select
Range("h9:I30,k9:k30").Select
Selection.ClearContents
ActiveSheet.Protect
ScreenUpdating = True
End Sub

<code>
</code>