Please Help!!!!!!

[cbcarpenter]

Im using excel 2007 and I have a worksheet with information in columns A-M. Some of the cells in M have specific dates and I need to know if i can have a formula that will copy and rows that have dates in Column M and place the copied rows into a separate worksheet within the workbook.

Also if its at all possible to have the formula also delete the date in column M from the original worksheet.

I hope this makes sense, kinda hard to explain. Please let me know if you can help in any way.

 

[Saagar]

Is this a one time operation or are you looking to automate using a macro/function to do this repeatedly.

 

[cbcarpenter]

I plan on using it as a macro

 

[Saagar]

Are you looking for a "specific date" in Column M or are you looking for all rows with any date?

 

[cbcarpenter]

Im looking for any dates. Thank you for any help you can give me.

 

[Saagar]

This macro will filter non-blank cells in Column M and copy all rows from column A to M. If your requirements are any different, let me know.

Test this and see if this works.

Sub ColumnMDateRows()

Dim rng As Range

With Application
    CurrentScreenUpdating = .ScreenUpdating
    CurrentCalculate = .Calculation
    CurrentEnableEvents = .EnableEvents
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

Worksheets("sheet1").Activate
ActiveSheet.Range("M:M").AutoFilter Field:=1, Criteria1:="<>"

With ActiveSheet
    Set rng = ActiveSheet.AutoFilter.Range
    filtered = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    
    If filtered > 0 Then
        rng.Offset(1, -12).Resize(rng.Rows.Count - 1, 13).Copy
        Worksheets("sheet2").Activate
        Cells(1, 1).Select
        ActiveSheet.Paste
    Else
        MsgBox ("No date rows found")
    End If
End With


With Application
    .ScreenUpdating = CurrentScreenUpdating
    .Calculation = CurrentCalculate
    .EnableEvents = CurrentEnableEvents
End With

End Sub

 

[cbcarpenter]

Saagar,
That code does work but for some reason it deletes all the rows from the main spreadsheet that doesnt have any dates in it.

I need the code to only delete the date from the main spreadsheet once its copied to sheet 2.

Let me know if I need to explain it better. Thank you!!!

 

[Saagar]

Cbcarpenter - The code doesn't delete non-dated rows, it just hides them (using filters). I will fix the code to unhide them again. Meanwhile, at the top of the Column M, you will notice a small arrow, if you click the arrow, you will have an option to show "All" data which unhides the other rows.

I will update the code to delete the date and send it later this morning.

 

[cbcarpenter]

Thank you so much Saagar. You are a life saver.

 

[Saagar]

Happy to help. Did you verify the file and were you able to "unfilter" the data?