Copy a row based on a date value

Needinghlp

New Member
Joined
Feb 27, 2013
Messages
25
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Needinghlp

New Member
Joined
Feb 27, 2013
Messages
25
Hello Needinghlp,

Another option is to use the Autofilter:-

VBA Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Needinghlp,

Just change this line of code:-

VBA Code:
.Offset(1).EntireRow.Copy

to
VBA Code:
.Offset(1, -14).Resize(, 37).Copy

I hope that this helps.

Cheerio,
vcoolio.
 

Needinghlp

New Member
Joined
Feb 27, 2013
Messages
25
Hello Needinghlp,

Just change this line of code:-

VBA Code:
.Offset(1).EntireRow.Copy

to
VBA Code:
.Offset(1, -14).Resize(, 37).Copy

I hope that this helps.

Cheerio,
vcoolio.
Resize! Yes that makes sense.. thank you very much - Again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,928
Messages
5,639,043
Members
417,066
Latest member
rhenman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top