Find the Matching Date and Copy Rows to Another Workbook

[saschultz]

Hello,

I have been trying a variety of methods to get this to work and none of them seem to work. I have a date on my worksheet that populates with today's date. If the value in sheet DataMaintenance, column G matches today's date (can be provided in a cell), I'd like the information from columns H, I, and J to copy over to the worksheet Dashboard2 starting in cell L3, M3 and N3, respectively .

The closest I have come is this code, but all it does is copy over all the data rather than filtering by the date:

Sub TestMacro2()
Dim SrchRng As Range
Dim ce1 As Range

Set SrchRng = Sheets("DataMaintenance").Range("G2:G12")

For Each ce1 In SrchRng
    If ce1.value = Sheets("Dashboard2").Range("A4").value Then
    Sheets("DataMaintenance").Select
    Range("H2").Select
    Selection.Copy
    Sheets("Dashboard2").Select
    Range("L3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    End If
Next ce1

End Sub

Thanks so much!

 

[Joe4]

Welcome to the Board!

I fixed up your code a little. See how this works for you:

Sub TestMacro2()

    Dim SrchRng As Range
    Dim ce1 As Range
    Dim nr As Long

    Application.ScreenUpdating = False

    Set SrchRng = Sheets("DataMaintenance").Range("G2:G12")

    For Each ce1 In SrchRng
        If ce1.Value = Sheets("Dashboard2").Range("A4").Value Then
'           Find last populated row in column L on Dashboard2 sheet
            nr = Sheets("Dashboard2").Cells(Rows.Count, "L").End(xlUp).Row + 1
            If nr < 3 Then nr = 3
            Sheets("DataMaintenance").Range(ce1.Offset(0, 1), ce1.Offset(0, 3)).Copy _
            Sheets("Dashboard2").Cells(nr, "L")
        End If
    Next ce1
    
    Application.ScreenUpdating = True

End Sub

 

[saschultz]

Thanks so much for your response!

I thought my code was working before but now neither version will do anything. Do you have any other suggestions for solving my problem?

Thanks!

 

[Joe4]

I think it would be helpful to see the current data on your two sheets.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Alternatively, if you are unable to do that, you can upload a sample file to a file sharing site (like DropBox), and provide a link to it.

Note: Just be sure to remove any sensitive information from your file first!