How to clear contents from filtered data then sort after copied data has been copied to new workbook

[pulsenation]

I am looking for an extra bit of VBA to add to the below to clear the contents of the copied data from the filtered data, then resort to remove the blank rows.

any ideas?

'This subroutine prompts the user to select dates
Public Sub PromptUserForInputDates()
ActiveSheet.Unprotect Password:="909"
Dim wsDB As Worksheet
 Dim StartDate As Date, EndDate As Date
 Set wsDB = ActiveWorkbook.Worksheets("Letters")
 With wsDB.Sort
 With .SortFields
 .Clear
 .Add Key:=wsDB.Range("A3:A50000" & LastRowTabel)
 .Add Key:=wsDB.Range("B3:B50000" & LastRowTabel)
 End With
 .SetRange wsDB.Range("A3:T50000" & LastRowTabel)
 .Header = xlNo 'Mogelijk xlNo, xlYes of xlGuess
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
 
 'Prompt the user to input the start date
 StartDate = InputBox("Please enter the start date")
 
 'Validate the input string
 If Not IsDate(StartDate) Then
 MsgBox "It looks like your entry is not a valid " & _
 "date. Please retry with a valid date...", vbCritical, "Input Error"
 Exit Sub
 End If
 
 'Prompt the user to input the end date
 EndDate = InputBox("Please enter the end date")
 
 'Validate the input string
 If Not IsDate(EndDate) Then
 MsgBox "It looks like your entry is not a valid " & _
 "date. Please retry with a valid date...", vbCritical, "Input Error"
 Exit Sub
 End If
 
 ''///check dates are valis to use
 If CLng(DateValue(StartDate)) > CLng(DateValue(EndDate)) Then
 MsgBox "The End Date value cannot be before the End Date. " & _
 "Please retry with a valid date...", vbCritical, "Input Error"
 Exit Sub
 End If
 'Call the next subroutine, which will produce the output workbook
 Call CreateSubsetWorkbook(Format(StartDate, "dd/mm/yyyy"), Format(EndDate, "dd/mm/yyyy"))
 
 
End Sub
'This subroutine creates the new workbook based on input from the prompts
Public Sub CreateSubsetWorkbook(StartDate As String, EndDate As String)
 Dim wbTo As Workbook
 Dim rRng As Range
 
 With Sheets("Letters")
 If Not .AutoFilterMode Then .Range("A2").AutoFilter
 .Range("A2").AutoFilter Field:=1, Criteria1:= _
 ">=" & CLng(DateValue(StartDate)), Operator:=xlAnd, Criteria2:="<=" & CLng(DateValue(EndDate))
 Set wbTo = Workbooks.Add
 .AutoFilter.Range.Copy ActiveSheet.Range("A3")
 .Range("A3").AutoFilter
 
 End With
 ActiveSheet.Columns.AutoFit
 ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & "Archive of P&P Tracker", 51
 ActiveWorkbook.Close True
 'Let the user know our macro has finished!
 MsgBox "Data transferred!"
 
 ActiveSheet.Protect Password:="909"
 
End Sub

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA is searching under US Date range and not UK. - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[pulsenation]

This is the only forum and first time I have asked this question so I am a bit at a loss to your comment Fluff. If you could point me in the right direction where it is a cross post i would be more than interested. Thanks.

 

[Fluff]

If you could point me in the right direction where it is a cross post

I already have, the link is in post#2.

This is the only forum and first time I have asked this question

Hogwash

 

[pulsenation]

I’m with you now.

My original question was answered on amendeding US dates to UK dates.

That question has been resolved and I am now asking a question that is no longer related to that question, hence why I have started a new thread.

 

[Fluff]

And you asked exactly the same question in the post I linked to, hence cross posted.

 

[pulsenation]

Apologies, I’m pretty new to the forum and still getting used to it. I’ve managed to answer my own question, so this thread is no longer valid. I’m not sure how to close the thread down.