Need a Macro to identify & then copy all rows containing dates within the past month.

Hi There

Total noob here !

I have 25,000 plus rows of data under the columns headed A to H in the worksheet named "Date Washed".
This raw data (so to speak) is generated by filtering and "washing" the contents of 2 other worksheets that are submitted to me weekly from remotely based administrators.

Column E of this "Date Washed" worksheet contains dates in the range 01/01/2009 to the current date, all of which appear scattered randomly throughout the full column.

I am trying (and failing !) to create a macro to find & select all the rows which contain a date in column E which is within 31 days of the current date (i.e all data collected in the last rolling months period)

These entire rows then need to be copied across to a separate new worksheet named "Last month only"

I hope this is enough info at this stage
Many thanks in advance

Hi

Many thanks for the quick response.

it almost worked and i can just about get my head round the basics of the code, unfortunately it "tripped up" when it encountered a cell with #value error in it (i can't seem to get these out of the washed data) and also seemed to be copying across the wrong data - e.g the dates it had copied upto the point that it went "pear shaped" were all for the year 2010 and not in any particular order rather than the dates within the last month

Could this be because the washed data populates with filtered data from other sheets within the same workbook ?

kind regards

A slighly diffrent aproach to solve this problem is using an autofilter.

Sub UseAutoF()

Dim lDate As Long

    lDate = Date - 31

    Range("A1:H1").AutoFilter Field:=5, Criteria1:=">=" & lDate
    
    ActiveSheet.AutoFilter.Range.Copy
    
    Sheets("Last month only").Range("A1").PasteSpecial Paste:=xlPasteAll
    
    Range("A1:H1").AutoFilter
    
    Application.CutCopyMode = False


End Sub

Alf

Hi Alf

many thanks for your response

this seemed to copy across about 800 rows (out of 10,000 ish) but in the range from 23/2/2010 to 16/8/2010 and not those in the last 31 days !?

could this be because the washed data cross references other sheet in the same work book ?

kind regards

Hi
Could you upload a file with a small sample of you data for testing purposes.

Alf

Hi Alf

file attached

this is a good example of the sort of data i have.
the most recent dated items are near the end of the sheet, but not always
there are also some occasional error values which need to be ignored when grabbing the date.


however, i may have partly answered my own question because some of the dates in this sample spontaneously changed form 2013 to 2010 while i was making the spreadsheet more readable.

probably due to cells referencing cells on other sheets perhaps?

would this account for the issues when running the code in the original workbook?
many thanks
kind regards

could this be because the washed data cross references other sheet in the same work book

No I don't think so mainly because I made a stupid mistake i.e. I dim'ed lData as Long (this makes date a number) instead of Date so try testing after changing dim line to

Dim lDate As Date

and see if that works better.

Alf

Correction tested both "lDate" as Long and "lDate" as Date and both works ok on the uploaded file from msexcelathome so there could be a problem with your data.

Also not sure which code you use still both should work fine so I think you need to upload a small sample file.

Alf

hi Alf

i will try and forward a more comprehensive sample of the whole workbook rather than just the sheet concerned, but please bear with me as i will need to remove confidential client info before i send the full workbook across.

many thanks
kind regards

Hi Alf

just tried your code on the sample i sent and it worked fine.

must be all the cross referencing to other worksheets.

that been the case, do you know how i can automatically copy an entire worksheet onto another work sheet as "Value" only to sever the cell references and then run this same code on that new sheet which should then hopefully work ?

hope that make sense

many thanks
kind regards

Try this one

Option Explicit
Sub CopyData()

Dim Lr1 As Long, DDate As Date, Counter As Long, ms As Worksheet
Application.ScreenUpdating = False

Set ms = Sheets("Last month only")
DDate = Date - 31

With Sheets("Data Washed")
Lr1 = .Cells(Rows.Count, "E").End(xlUp).Row
For Counter = 2 To Lr1
    If Not IsError(.Cells(Counter, "E")) Then
      If .Cells(Counter, "E").Value > DDate Then
        .Cells(Counter, "A").Resize(, 8).Copy
         ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
      End If
    End If
Next Counter
Application.CutCopyMode = False
End With
ms.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

how i can automatically copy an entire worksheet onto another work sheet as "Value" only

Perhaps a macro like this (assumes both sheets in same workbook):

Sub TransVal()

Sheets(”From sheet”).Activate

ActiveSheet.UsedRange.Copy

Sheets(”To sheet”).Range(”A1”). PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
End Sub

Alf

Ps Since I'm off on a 10 day tripp today I will be unable to give any more help but fortunately AB33 has taken an interest in your problem and I'm sure he will be helping you with any other problems.

Cheers Alf

Have a good trip

I hope i have this sorted before you get back
kind regards

Have you tried my code post # 11?