Cut and paste cell rows to a new workbook when specific cell populated

Aggthemagdag

New Member
Joined
Apr 20, 2017
Messages
1
Hello, all. I hope someone can help me. It's probably quite a simple solution but I have literally no knowledge of VBA or the more advanced workings of Excel. I have a spreadsheet that lists customers and attempted phone calls made to them. When we make contact, a date is input in column "M". The data is no longer needed there but it does need to be stored on a master archive for review. So what I would like is a macro or VBA code (whatever is best/easier) to take the rows from the source sheet that have a date in column "M" and move that data to a master archive workbook. The rows where column "M" is blank to then move up and when it is run again, for the information going to the master to go on the next available row. I have tried several different codes and the closest I have got is shown below but it seems to copy the formatting of the cell above. Thank you in advance...

HTML:
'Using autofilter to copy rows that have a completed date field to a new worksheet
 
Dim LR As Long
Dim LR1 As Long
Range("A3").EntireRow.Insert Shift:=xlDown
LR = Sheets("Sheet1").Cells(Rows.Count, "M").End(xlUp).Row
LR1 = Sheets("Sheet3").Cells(Rows.Count, "M").End(xlUp).Row
    With Sheets("Sheet1").Range("M3:M" & LR)
        .AutoFilter Field:=1, Criteria1:="<>", VisibleDropDown:=True
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet3").Range("A" & LR1)
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub

Andrew
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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