Copy, Paste loop based on today's Date

[oscarmiike]

Hi all,

I’ve been searching and searching for the solution to this problem for a couple of days now, however given my inexperience with excel VBA programming it’s proving difficult to pin down a solution to my problem. I’d appreciate any help you can offer. Here’s what I’m after:

In the attached spreadsheet I’d like to copy the value in “A1” (IRS) and paste to (Extract) only if the adjacent cells date is equal to today’s date. I need something that’s going to work through the visible cells (I’ve been looking at For each) until the end of the row, and also not do anything if the sheet is blank, which will sometimes occur.

If you need more information please let me know!

Thank heaps,
Oscarmiike.

[JBeaucaire]

Maybe like so:

Option Explicit

Sub IFS()
Dim CopyRNG As Range, c As Range

For Each c In Sheets("IRS").Range("B2:B" & Rows.Count).SpecialCells(xlConstants, 1)
    If CDate(c.Value) = CDate(Date) And Rows(c.Row).Hidden = False Then
        If CopyRNG Is Nothing Then
            Set CopyRNG = c.Offset(, -1)
        Else
            Set CopyRNG = Union(CopyRNG, c.Offset(, -1))
        End If
    End If
Next c

If Not CopyRNG Is Nothing Then CopyRNG.Copy Sheets("Extract").Range("B5")

End Sub

[oscarmiike]

Thanks JBeaucaire for your prompt reply.

Unfortunately I'm not getting any results from the code, it's a little too complicated for me to reverse engineer also. Any thoughts?

Cheers!

[JBeaucaire]

The macro reads:

For each numeric cell in column B of the IRS sheet, if the date in that cell matches today's date and the row it is on is not hidden, copy the value from column A of that row and paste all the found cells into column B of the Extract sheet.


From the sample data you posted, I had to change several dates to 1/14 before i got results, too. Did you do that?

[oscarmiike]

Hi JBeaucaire! I was able to make it work based on your advice, thanks so much. Unfortunately I've discovered that the columns which I'm referencing aren't static! I need to be able to pull the data based on the title of the column in row 1, so the offset isn't going to work any more can you help me make the CopyRNG more dynamic?

Sub IRS_TradeNumber()

Dim CopyRNG As Range
Dim c As Range

For Each c In Sheets("IRS").Range("YX2:YX1000")
    If c.Value = Date And Rows(c.Row).Hidden = False Then
        If CopyRNG Is Nothing Then
            Set CopyRNG = c.Offset(, 2)
        Else
            Set CopyRNG = Union(CopyRNG, c.Offset(, 2))
        End If
    End If
Next c

If Not CopyRNG Is Nothing Then CopyRNG.Copy Sheets("Extract").Range("D1048576").End(xlUp).Offset(1)

End Sub

Cheers!
Oscarmiike

[JBeaucaire]

Sure, dynamic to what specification?

[oscarmiike]

Based on a string value, in this specific one the title of the column is "Trade Number"

Thanks :D

[JBeaucaire]

Try this:

Option Explicit

Sub IFS()
Dim CopyRNG As Range, Col As Long, c As Range

With Sheets("IRS")
    On Error Resume Next
    Col = WorksheetFunction.Match("Trade Number", .Rows(1), 0)
    If Col = 0 Then
        MsgBox "The 'Trade Number' column was not found"
        Exit Sub
    End If

    For Each c In .Range(.Cells(2, Col), .Cells(.Rows.Count, Col)).SpecialCells(xlConstants, 1)
        If CDate(c.Value) = CDate(Date) And Rows(c.Row).Hidden = False Then
            If CopyRNG Is Nothing Then
                Set CopyRNG = .Range("A" & c.Row)
            Else
                Set CopyRNG = Union(CopyRNG, .Range("A" & c.Row))
            End If
        End If
    Next c

    If Not CopyRNG Is Nothing Then CopyRNG.Copy Sheets("Extract").Range("D" & Rows.Count).End(xlUp).Offset(1)
End With

End Sub

[oscarmiike]

Thanks for the quick reply! I can't get it to work though, doesn't seem to find the Column. I've attached a sample:

FYI the sample doesn't have enough data to get through the full procedure, however once it finds the column the rest will work on my version

[JBeaucaire]

My apologies, the error was in the variable type, it should be LONG. Edited above and tested as working on your workbook.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[oscarmiike]

You're a legend! Works perfectly - Could I trouble you for one more little adjustment though?

Is it possible to do a 'match' function to find the date column also? So match the "Trade Number" column, then match "Trade Date", then perform the 'for each' copy procedure based on the "Trade Date" being today? I've tried hacking up your code.. not surprisingly with no success

I really appreciate your help, this should be the final request!

Cheers,
oscarmiike