Solved

Copy Rows from one sheet where the date > the latest date on another sheet

Posted on 2015-01-28
8
386 Views
Last Modified: 2016-02-10
Hi,

In the attached example, I want to find the latest date in column A on sheet 1, then copy all rows from sheet 2 to sheet 1 where the date in column A on sheet 2 is greater than the latest date in column A on sheet 1.

In my example, the solution would copy the 3 highlighted rows from sheet 2 to sheet 1, appending at the end of the existing data.

I would use this on data sets that are many thousands of rows so the solution shouldn't be based on how many rows are in this example.

Thanks in advance,

swjtx99
Example.xlsx
0
Comment
Question by:swjtx99
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 200 total points
ID: 40576764
Try

Sub movlatest()
    Set tws = Sheets("Sheet1")
    Set sws = Sheets("Sheet2")
    ld = WorksheetFunction.Max(Range("A:A"))
    For Each cel In sws.Range("A:A")
        If cel.Value <> "" Then
            If cel.Value > ld Then
                cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)
            End If
        End If
    Next cel
End Sub
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 40576920
Here is the code that could do that with no FLAW.
Pls note that the code that Saquib posted is fine except that it has a flaw if you are positioned in sheet2 and activate the macro then you will only get the header copied ! if you are positioned in Sheet1 and activate the macro it will copy the header + the correct records.

For this reason only I posted my code.
Sorry for this.

Sub CopyLatestRows()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim cCell As Range
Dim MaxDate As Date
Dim I As Long, MaxRow1 As Long, MaxRow2 As Long

Set WS1 = Sheets("Sheet1")
MaxRow1 = WS1.Range("A" & WS1.Rows.Count).End(xlUp).Row
MaxDate = Application.WorksheetFunction.Max(WS1.Range("A:A"))

Set WS2 = Sheets("Sheet2")
MaxRow2 = WS2.Range("A" & WS2.Rows.Count).End(xlUp).Row

For I = 2 To MaxRow2
    If WS2.Cells(I, "A") > MaxDate Then
        WS2.Cells(I, "A").EntireRow.Copy WS1.Cells(MaxRow1, "A")
        MaxRow1 = MaxRow1 + 1
    End If
Next I

MsgBox "Transfer Done."
End Sub

Open in new window


and here is the workbook attached just run the macro that is there.
gowflow
Example-V01.xlsm
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40577194
You can also do this without VBA. You can use Advanced Filter function.

If you set the AF to filter in place rather than copy to another location, you can then copy and paste the results to append to the other sheet.

AF requires a subset table of data with at least one header and one criteria beneath it. This would be your date header and a formula to pull the Max date from existing data:

Date Header
=">"&MAX(Sheet1!A:A)

Thanks
Rob H
0
 

Author Closing Comment

by:swjtx99
ID: 40578870
Hi,

Thanks to both Saqib and goflow. Both solutions worked so I thought a point distribution would be fair and I gave a slight edge to goflow since his solution doesn't copy the header row. I tried both on sheets with many thousands of lines and both seemed to run in about the same time (~5 sec)

Many thanks,

swjtx99
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 40579400
Your welcome.
gowflow
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40579410
Did you try the Filter option or am I just being ignored??
0
 

Author Comment

by:swjtx99
ID: 40615061
Hi Rob,

Very sorry for the delayed reply. I've been out of pocket lately.

I needed a vba option, sorry I didn't specify but thought that was clear. Also apologize for not acknowledging your proposed solution. I'll be more careful in the future and I appreciate you taking the time to offer your expertise.

Thanks,

swjtx99
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40615381
Apologies accepted. I was probably having a bad day as well.

The filter option could have been included in a VBA routine.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now