Compare Two Dates and if No difference then copy Specific Cell value and paste to another sheet

[Gogo007]

Hello,

I have sheet1 in that I have a column N "Due Date" {Date format} and Column V "Invoice Due Date" {Date format}, if both the values are the same then copy the Amount from Column X from that row and Paste to Sheet2.

I request need a VBA code for this.
I have attached the snap for better understanding.

Please if anyone can help me out.

 

[Sahak]

Hi Gogo,

Try this

Sub Copy_to_Sheet2()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Range(Range("N65000").End(xlUp), Range("N65000").End(xlUp).End(xlUp).Offset(1)).Name = "DueDate_Rng"
    Dim SGcell As Range
    For Each SGcell In Range("DueDate_Rng")
        If SGcell.Value = SGcell.Offset(0, 8).Value Then
            SGcell.Offset(0, 11).Value = "V"
            Sheets("Sheet2").Range("A65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 8).Value
            Sheets("Sheet2").Range("B65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 10).Value
        Else
            SGcell.Offset(0, 11).Value = "X"
        End If
    Next
End Sub

By the way, your file should be saved as Excel Macro-Enabled Workbook (*.xlsm)

 

[Gogo007]

Hi Gogo,

Try this

Sub Copy_to_Sheet2()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Range(Range("N65000").End(xlUp), Range("N65000").End(xlUp).End(xlUp).Offset(1)).Name = "DueDate_Rng"
    Dim SGcell As Range
    For Each SGcell In Range("DueDate_Rng")
        If SGcell.Value = SGcell.Offset(0, 8).Value Then
            SGcell.Offset(0, 11).Value = "V"
            Sheets("Sheet2").Range("A65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 8).Value
            Sheets("Sheet2").Range("B65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 10).Value
        Else
            SGcell.Offset(0, 11).Value = "X"
        End If
    Next
End Sub

By the way, your file should be saved as Excel Macro-Enabled Workbook (*.xlsm)

Thanks for the input.
Just to make sure
Sheet1 in Column N has 1st date and Sheet1 in Column U has 2nd date and to compare and copy the value from Sheet1 in Column X paste to Sheet2 from D4 to down...d5,d6,d7,d8 (if date matches from Sheet1).

Many Thanks.

 

[Sahak]

Hi Gogo,

This VBA code does:

• Assigns name "DueDate_Rng" to range N9:N12
• Loops “"DueDate_Rng" & comparing (matching) dates in columns “N” & “V”
• If there is a match,
  • letter “V” will be entered on the same row in column “Y”
  • The date & amount from column “X” will be copied in “Sheet2”, columns “A” & “B”

If no match, the letter “X” will be entered on the same row in column “Y”

 

[Gogo007]

Hi Gogo,

This VBA code does:

• Assigns name "DueDate_Rng" to range N9:N12
• Loops “"DueDate_Rng" & comparing (matching) dates in columns “N” & “V”
• If there is a match,
  • letter “V” will be entered on the same row in column “Y”
  • The date & amount from column “X” will be copied in “Sheet2”, columns “A” & “B”

If no match, the letter “X” will be entered on the same row in column “Y”

Hi Sahak,

Thanks for the guidance,
It's working fine after changing some row no.
Need one suggestion, I am applying this code on filtered row (visible cells), but this code is running for all the cells, so can you please help me with that.
The code should consider only visible cells.

Thanks

 

[Sahak]

Hi Gogo,

I think this is what you need

Sub Copy_Visible_to_Sheet2()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Range(Range("N65000").End(xlUp), Range("N65000").End(xlUp).End(xlUp).Offset(1)).Name = "DueDate_Rng"
    Dim SGcell As Range
    For Each SGcell In Range("DueDate_Rng")
        Dim RowNo As Long
        RowNo = SGcell.Row
        If Not ActiveSheet.Rows(RowNo).EntireRow.Hidden Then
            If SGcell.Value = SGcell.Offset(0, 8).Value Then
                SGcell.Offset(0, 11).Value = "V"
                Sheets("Sheet2").Range("A65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 8).Value
                Sheets("Sheet2").Range("B65000").End(xlUp).Offset(1).Value = SGcell.Offset(0, 10).Value
            Else
                SGcell.Offset(0, 11).Value = "X"
            End If
        End If
    Next
End Sub

 

[Sahak]

Hi Gogo, did you try the last code I sent?

 

[Gogo007]

Hi Gogo, did you try the last code I sent?

Yes, Fantastic. Thanks a lot for help

 

[Sahak]

You are very welcome. Glad to be helpful.