Hi:
I have a spreadsheet that I am trying to take some data from one workbook in the spreadsheet and move it to another workbook. I need to do this move based on a column called end date. If the end date is today -30 days then I want the row moved to the other sheet. The code I have works if I hardcode the date in but I can't seem to put a formula in and make it work. Could someone help? Thanks!! The code is below.
Sub Archive()
'Sheet module code, like: Sheet1
Dim myRng As Range, c As Range, myBott&
myBott = Range("A65536").End(xlUp).Row
Set myRng = Range(Cells(1, 16), Cells(myBott, 1))
For Each c In myRng
If UCase(c.Value) = "11/29/2005" Then _
c.EntireRow.Cut _
Destination:=Worksheets("Complete Events").Range("A65536").End(xlUp).Offset(1, 0)
Next c
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub
I have a spreadsheet that I am trying to take some data from one workbook in the spreadsheet and move it to another workbook. I need to do this move based on a column called end date. If the end date is today -30 days then I want the row moved to the other sheet. The code I have works if I hardcode the date in but I can't seem to put a formula in and make it work. Could someone help? Thanks!! The code is below.
Sub Archive()
'Sheet module code, like: Sheet1
Dim myRng As Range, c As Range, myBott&
myBott = Range("A65536").End(xlUp).Row
Set myRng = Range(Cells(1, 16), Cells(myBott, 1))
For Each c In myRng
If UCase(c.Value) = "11/29/2005" Then _
c.EntireRow.Cut _
Destination:=Worksheets("Complete Events").Range("A65536").End(xlUp).Offset(1, 0)
Next c
Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub