Dynamic dates in VBA

**Fairport** · 04-23-2009, 10:06 AM

Hello everyone, I’m new to the forum and still teething in VBA.
My spread sheet holds data with input dates recorded. Periodically there is a requirement to remove and copy elsewhere data that is older than 365 days using the Auto Filter. I have a macro that , on first pass, selects the required dates older than 365 days copies and pastes but on the 2nd execution of the macro with a different date required it doesn’t work. Looking at the code the date is not changed. Is there a way around this problem? I am also experiencing a problem finding the next available empty cell in which to copy. I have used serial numbers for dates having first had trouble with dates in dd/mm/yyyy format. I would be grateful for any guidance thank you.

**elcaris** · 04-23-2009, 12:02 PM

You may want to try this:

Sub TransferOldData()
    
    Dim rData As Range
    Dim rOldData As Range
    
    For Each rData In Range("Data!A2:A65536")
    If rData.Value <> "" Then
        If rData.Value < Range("Data!H2").Value Then
            Set rOldData = Range("OldData!A65536").End(xlUp).Offset(1, 0)
            For i = 0 To 3
            rOldData.Offset(0, i).Value = rData.Offset(0, i).Value
            rData.Offset(0, i).Value = ""
            
            Next i
        End If
    End If
    Next rData
    
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A2:D65536")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

**Fairport** · 04-23-2009, 05:49 PM

Thank you Elcaris. Having copied and pasted your example into a new module it halts at

Set rOldData = Range("OldData!A65536").End(xlUp).Offset(1, 0)

What do you suggest? Thanks again

**martindwilson** · 04-23-2009, 06:50 PM

looks like you need sheets named data! and olddata!
have you changed sheetname olddata! in code to match your sheet name. (or rename your sheet to olddata)