VBA to copy rows where Date is greater than mentioned in cell

LUQ

New Member
Joined
Apr 28, 2022
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
I have a excel file with two sheets. Secon tab stored all previosu data and in the first tab I am adding new data every day. I need to comapre dates in column from 1st sheet and 2nd sheet and then copy from 1st tab only those lines where date is greater then in second tab (of course copy and paste data ti 2nd tab just after last row) .
What is more and most important for me I need to copy only data from 1st column from 1st tab not entire row.

Below code is almost perfect but at the end entire row is copying to the second tab but I need only data from 1st column,

Sub future()
Dim sh As Worksheet, lr As Long, rng As Range, sh2 As Worksheet, lr2 As Long
Set sh = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit Sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A3:A" & lr)
For Each c In rng
If DateValue(c.Value) >= DateValue(Date) Then
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1
c.EntireRow.Copy sh2.Range("A" & lr2)
End If
Next
End Sub

I want to use Your Code but A bit modified. I need copy not entire row but only data from 1st column. I mean I have two sheets and in column L we have 'created on date'. I have to copy ID's from tab1 (column A) and past into tab 2 column A (into first free row) only those rows where data in column L tab 1 is newest then date in column L tab2.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
If you only need to copy the first column and not the whole row, then try changing this line:
VBA Code:
c.EntireRow.Copy sh2.Range("A" & lr2)
to this:
VBA Code:
Cells(c.Row, "A").Copy sh2.Range("A" & lr2)
 
  • Like
Reactions: LUQ
Upvote 0
Solution
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

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
Back
Top