Copying rows based on a matching date

[mps80y]

Hi All,

I've found so many variations on how to do this, but none seem to fit exactly what I'm after so I'm getting lost in that spiral - so I would appreciate any advice/help.

I have a spreadsheet that has several sheets.

Sheet 'Calc_Sheet' contains a value in cell E1 which lists the last date of the previous month in the format dd/mm/yyyy and is formatted as a Date field - *dd/mm/yyyy.

I have another sheet 'Previous Month' that has all the data of bookings made. Column C in that sheet also has a date in the format dd/mm/yyyy and is also formatted as a Date field - *dd/mm/yyyy

I need to copy all the rows from the 'Previous Month' sheet, that match the date in cell E1 in sheet 'Calc_Sheet' and copy them to the end of a third sheet 'Current Month' and then sort that sheet based on column A.

Is this something easily done?

TIA guys :D

 

[mumps]

Try this macro. Change the column letter (in red) to the last used column in the "Previous Month" sheet.

Sub copyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, sDate As String, desWS As Worksheet
    Set desWS = Sheets("Current Month")
    sDate = Sheets("Calc_Sheet").Range("E1")
    With Sheets("Previous Month")
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("C1:C" & LastRow).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, CDate(sDate))
        .Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("C1").AutoFilter
    End With
    desWS.Sort.SortFields.Clear
    desWS.Sort.SortFields.Add Key:=Range("A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With desWS.Sort
        .SetRange Range("A1:[COLOR="#FF0000"]G[/COLOR]" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[mps80y]

Hi mumps,


Sorry for the delay, I get a run time error '1004': Autofilter method of Range class failed. And when I hit debug it returns to the following line:


.Range("C1:C" & LastRow).AutoFilter Field:=1, Operator:=xlFilterValues, Criteria2:=Array(1, CDate(sDate))


I've attached a sample of the spreadsheet I'm using - can you please have a look and let me know what you recommend?


Link to sample file: http://s000.tinyupload.com/index.php?file_id=04622185165133911592

TIA

 

[mumps]

Try:

Sub copyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, LastRow2 As Long, sDate As String, desWS As Worksheet
    Set desWS = Sheets("Current Month")
    sDate = Sheets("Calc_Sheet").Range("E1")
    With Sheets("Previous Month")
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A1:W" & LastRow).AutoFilter Field:=3, Operator:=xlFilterValues, Criteria2:=Array(2, CDate(sDate))
        .Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("C1").AutoFilter
    End With
    LastRow2 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Sort.SortFields.Clear
    desWS.Sort.SortFields.Add Key:=Range("A2:A" & LastRow2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With desWS.Sort
        .SetRange Range("A1:W" & LastRow2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[mps80y]

Still getting the same error - this time on line;

.Range("A1:W" & LastRow).AutoFilter Field:=3, Operator:=xlFilterValues, Criteria2:=Array(2, CDate(sDate))

It's adding the filters to row 1 and then stops at that point.

 

[mumps]

It works without errors for me. Click here to download your file.

 

[mps80y]

It works without errors for me. Click here to download your file.

I downloaded that file and tried to run it but I still keep getting the same error. When I hit Debug it takes me to the same line;

.Range("A1:W" & LastRow).AutoFilter Field:=3, Operator:=xlFilterValues, Criteria2:=Array(2, CDate(sDate))

Am I doing something wrong?

 

[mps80y]

I worked out what the issue is. Its to do with the date format - I'm in Australia and we use the format dd/mm/yyyy, when I changed my computers format settings to mm/dd/yyyy it worked perfectly. Any suggestions on how I can fix this?

 

[mps80y]

I've re-uploaded the file here

 

[mumps]

Working with dates and regional settings is very tricky. Unfortunately, I don't have any experience in that area. Could I suggest that you start a new thread posting a link to your file and explaining the problem. If you find a solution, I would appreciate it if you could send me a private message with a link to that solution. It would be valuable information that I might be able to use in the future.