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

pulsenation

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
'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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.
 

pulsenation

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
And you asked exactly the same question in the post I linked to, hence cross posted.
 

pulsenation

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

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
Top