Copy in a new sheeet, rows that have a certain date in them

[daniels012]

Using code:
How can I copy rows that have specific dates from a sheet named "Total" to the current sheet I am in?

Rather than manually copying and pasting, it would be much easier if I could do it using code.

Any Ideas?

Michael

 

[daniels012]

I am soooooo close
I just can figure out what to put here:
If c.Value <= "12/18/2005"
I need the cell value to be the previous month from the current one!!!!


Michael

 

[kmiles]

in Total Worksheet cell E2 type =today()

replace IF statement with:


If format(c.Value,"MMM") = Format(DateAdd("m", -1, e2), "MMM") Then


This will copy all rows where the MONTH of the date in cell is = the MONTH of today - one month

 

[daniels012]

That is soooooooooo close.
It gives me November from every year previous!! If I use Year as well in the formula, will that work? I know it won't work for January 2006 but I'll get over it!!


Thank you KMiles

Michael

 

[kmiles]

TRY THIS:

If format(c.Value,"MMM-YYYY") = Format(DateAdd("m", -1, e2), "MMM-YYYY") Then

 

[daniels012]

Nope
Nothing copied

??

 

[kmiles]

That should work.

You may have to post some sample data.

 

[daniels012]

Here is the part of what I have that pertains to dates:

Dim c As Range, ws As Worksheet
    
    Set ws = Worksheets("Running Total")

    For Each c In ws.Range("K:K")
    If Format(c.Value, "MMM-YYYY") = Format(DateAdd("m", -1, e2), "MMM-YYYY") Then
    c.EntireRow.Copy
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Select
    End If
    Next

I could enter a range on the sheet in cells E3 and E4. that has the start and end date and then somehow enter a between function???

Michael

 

[daniels012]

What can I change?????

If Format(c.Value, "MMM-YYYY") = Format(DateAdd("m", -1, e2), "MMM-YYYY")


Michael

 

[daniels012]

This is the one that worked the best. What can I add to get the year also?
If format(c.Value,"MMM") = Format(DateAdd("m", -1, e2), "MMM") Then

Michael

 

[daniels012]

WON'T GET THE rows to copy? Any ideas?

Michael