Copy a row based on a date value

[Needinghlp]

Hi all,

Hoping someone might be able to offer some guidance. I have 2 worksheets (sheet1 & sheet2). Sheet 2 contains a list of dates in column O.
I am trying to create some code that will identify dates after 30/06/2021 on sheet2, copy the relevant row and paste it to sheet1.
Below is the code I have. I've also tried putting 30/06/2021 in a reference cell (BB1) and used this line of code instead of whats below, but that doesn't work either: If Worksheets("Sheet2").Cells(i, 15).Value > ("BB1") Then

Private Sub CommandButton10_Click()

A = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To A

If Worksheets("Sheet2").Cells(i, 15).Value > 30 / 6 / 2021 Then

Worksheets("Sheet2").Rows(i).Copy
Worksheets("Sheet1").Activate
b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet1").Cells(b + 1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues

End If

Next

End Sub

 

[vcoolio]

Hello Needinghlp,

I'm glad that I was able to help and that it's all working as you would like.
It's odd, though, that you received an error on the Application line. Those two lines of code basically just turn off/on screen updating while the code executes so that it's all a seamless process. A spelling error perhaps?
Anyway, all the best with your project and thanks for the feed-back.

Cheerio,
vcoolio.

 

[Needinghlp]

Hello Needinghlp,

Another option is to use the Autofilter:-

Private Sub CommandButton10_Click()

        Dim pDt As Long: pDt = Sheet2.[BB1].Value
       
Application.ScreenUpdating = False

        With Sheet2.Range("O2", Sheet2.Range("O" & Sheet2.Rows.Count).End(xlUp))
                .AutoFilter 1, ">" & pDt
                .Offset(1).EntireRow.Copy
                Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
       
Application.ScreenUpdating = True

End Sub

I've assumed:-
- You have headings in Row2 of Sheet2.
- The reference cell (BB1) is in Sheet2 as well.

You'll need to format Column O in Sheet1 as Date.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio... you helped me with the above code recently and I'm trying to adjust it so that rather than copy the full row the value in BB1 is met, I copy just a range from a to ak. I've been trying to adjust the entirerow.copy but am not quite getting it (I'm a novice and not particularly familiar with the autofilter code). Are you able to offer any suggestions? Many thanks.

 

[vcoolio]

Hello Needinghlp,

Just change this line of code:-

.Offset(1).EntireRow.Copy

to

.Offset(1, -14).Resize(, 37).Copy

I hope that this helps.

Cheerio,
vcoolio.

 

[Needinghlp]

Hello Needinghlp,

Just change this line of code:-

.Offset(1).EntireRow.Copy

to

.Offset(1, -14).Resize(, 37).Copy

I hope that this helps.

Cheerio,
vcoolio.

Resize! Yes that makes sense.. thank you very much - Again

 

[vcoolio]

You're welcome!
Thanks for the feed back.

Cheerio,
vcoolio.