VBA code to copy & paste data as per Date Criteria

Good day Team, I have already posted same question of some other form but it been long time and did not get any response so, I have hope that I will get some result here. I am new to VBA, so trying...

Macros and VBA

HansVogelaar

Jan 03, 2022

doit_2729

Thanks, I see what is happening. Merged cells don't play nice with VBA. Try this version:

Private Sub CommandButton1_Click()
    Dim Rg As Range
    Set Rg = Me.UsedRange.Columns(1).Find(What:=Application.Text(Date, [A16].NumberFormat), LookIn:=xlValues)
    If Rg Is Nothing Then
        MsgBox "Today's Date Not Found. Please check the 'Date Received'"
    ElseIf Rg.Offset(0, 1).Value = "" Then
        Rg.Offset(0, 1).Resize(, 22).Value2 = [B16:W16].Value2
    ElseIf Rg.Offset(0, 1).Offset(1).Value = "" Then
        Rg.Offset(0, 1).Offset(1).Resize(, 22).Value2 = [B16:W16].Value2
    ElseIf Rg.Offset(0, 1).Offset(2).Value = "" Then
        Rg.Offset(0, 1).Offset(2).Resize(, 22).Value2 = [B16:W16].Value2
    Else
        MsgBox "All three times have been filled!"
    End If
    Application.CutCopyMode = False
End Sub

HansVogelaar

MVP

Jan 03, 2022

doit_2729

Thanks, I see what is happening. Merged cells don't play nice with VBA. Try this version:

Private Sub CommandButton1_Click()
    Dim Rg As Range
    Set Rg = Me.UsedRange.Columns(1).Find(What:=Application.Text(Date, [A16].NumberFormat), LookIn:=xlValues)
    If Rg Is Nothing Then
        MsgBox "Today's Date Not Found. Please check the 'Date Received'"
    ElseIf Rg.Offset(0, 1).Value = "" Then
        Rg.Offset(0, 1).Resize(, 22).Value2 = [B16:W16].Value2
    ElseIf Rg.Offset(0, 1).Offset(1).Value = "" Then
        Rg.Offset(0, 1).Offset(1).Resize(, 22).Value2 = [B16:W16].Value2
    ElseIf Rg.Offset(0, 1).Offset(2).Value = "" Then
        Rg.Offset(0, 1).Offset(2).Resize(, 22).Value2 = [B16:W16].Value2
    Else
        MsgBox "All three times have been filled!"
    End If
    Application.CutCopyMode = False
End Sub

doit_2729

Copper Contributor

Jan 03, 2022

Awesome.! It's working the way i need.
You are great Sir.
I cannot thank you enough for helping me out.
“Thanks a bunch! Highly appreciated for your time to look in to my request.

Forum Discussion

VBA code to copy &amp; paste data as per Date Criteria

VBA code to copy & paste data as per Date Criteria