VBA: how to change cell reference

[cmclux]

I have written the following copy and paste macro which now references to one cell and the copy and paste works. I have a range of data in one row that I would like to use the same macro for but when I change the cell reference to a range the macro does not work.

Private Sub Workbook_Open()
'
' Macro1 Macro
'

'
If Range("D3") <= Range("$B$2") Then
Range("D42").Copy
Range("D42").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

End Sub

The macro compares a date in cell D3 with todays date in cell B2 and then copies and replaces values in cell D42. The references to D3 and D42 need to reference to column E, F etc.

 

[AlphaFrog]

Try something like this...

Private Sub Workbook_Open()

    Dim col As Integer
    
    With Sheets("Sheet1")
    
        For col = 4 To 10 ' For each column D to J
            If .Cells(3, col).Value <= Date Then .Cells(42, col).Value = .Cells(42, col).Value
        Next col
    
    End With

End Sub

 

[cmclux]

That works great, thank you!