Please Help!!!!!!

cbcarpenter

New Member
Joined
Apr 28, 2011
Messages
17
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Is this a one time operation or are you looking to automate using a macro/function to do this repeatedly.
 
Upvote 0
Are you looking for a "specific date" in Column M or are you looking for all rows with any date?
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top