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...
Andrew
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