Add a condition to my copy and paste from one sheet to another based on matching dates

Question

I'm fairly new at VBA and am stuck and would like some help. I need a code with a condition to match the date in cell(J1) in sheet1 ("Exec Summary") to a range of dates (A3:A60) in sheet2("Monthly comments") and if the condition is met to copy the rows of data in cells "S3:S30" in sheet1("Exec Summary") and paste a transpose version of the data to sheet2 where the date matched.

At the moment I have a button and this contains a code which copies the commentary section (Cell S) in my sheet 1 "Exec Summary" and pastes a transpose of that comment section from "Exec Summary" into the other sheet "Monthly comments" if the row is empty. If there is anyway to adjust this so that it has a condition of when the date in a cell in "Exec Summary" sheet matches the date in a given set of the "Monthly Comment" Sheet then it will paste the transpose data will be preferred.

Private Sub CommandButton1_Click()
    With Sheets("Monthly Comments")
        .Cells(.Range("C" & .Rows.Count).End(xlUp).Row + 1, 3).Resize(1, 30).Value = Application.WorksheetFunction.Transpose(Sheets("Exec Summary").Range("S3:S32").Value)
    End With
End Sub

gaback gaback · Accepted Answer · 2019-09-12T14:27:53

Private Sub CommandButton1_Click()
    Dim i as integer: i = 0
    With Sheets("Monthly Comments")  
        i = WorksheetFunction.Match(Sheets("Exec Summary").Range("J1").Value2, .Range("A2:A60"), 0)
        if i > 0 then
            .Cells(i, 3).Resize(1, 30).Value = Application.WorksheetFunction.Transpose(Sheets("Exec Summary").Range("S3:S32").Value)
        end if
    End With
End Sub

This code try to find a match of the date at J1 in S3:S30. If it found any then you copy & paste

Add a condition to my copy and paste from one sheet to another based on matching dates

1 Answers