VBA Greater Than Date issue with Autofilter on a new workbook

V

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi

Everything is working fine, only one thing, when the macro moves the selected data onto a new workbook as per the attached screenshot capture1. This is good so far but the issue is that the autofilter is still visible. When I remove the autofilter it expands the whole data for the 214 records instead of 4 records. My code is as below using Greater Than in VBA. I have tried to use paste special and this doesn’t work. What is the best way to do this?

' Input Greater than

ActiveSheet.Range("A1").AutoFilter Field:=7, Criteria1:=">" & Application.InputBox("Great Than Date"), Operator:=xlAnd

'Move to new workbook

ActiveSheet.Move

Ideally it would be as the attached screenshot capture 2. I run a macro, I get an inputbox to allow me to enter the date using the Greater than. This will select the data and move it to a new workbook. Hope this makes sense?

Your help is much appreciated on this matter.

Thank you

Regards
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    70.2 KB · Views: 13
  • Capture2.PNG
    Capture2.PNG
    113.7 KB · Views: 12

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi,

why not save the date you asked for in a variable and use that in the new workbook with a reversed filter to delete all those records that do not fit. Another way is to add an empty workbook and copy/paste the visible range there.

First one may look like this where ActiveSheet is copied, original stays in workbook:

VBA Code: 
Public Sub MrE_1228116_1701A13()
' https://www.mrexcel.com/board/threads/vba-greater-than-date-issue-with-autofilter-on-a-new-workbook.1228116/
'copy Activesheet to new workbook, delete non-fitting data
Dim varAns As Variant
Dim lngDate As Long

varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy", "Date for Filtering", Type:=2)
If varAns = False Then Exit Sub
lngDate = CLng(DateValue(varAns))

ActiveSheet.Range("A1").AutoFilter Field:=7, Criteria1:=">" & lngDate, Operator:=xlAnd

'Move to new workbook
'Activesheet.Move
ActiveSheet.Copy

With ActiveSheet
  .Range("A1").AutoFilter Field:=7, Criteria1:="<=" & lngDate, Operator:=xlAnd
  .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  .Range("A1").AutoFilter
End With

End Sub

VBA Code: 
Public Sub MrE_1228116_1701A13_2()
' https://www.mrexcel.com/board/threads/vba-greater-than-date-issue-with-autofilter-on-a-new-workbook.1228116/
'copy visible range to new workbook
Dim varAns As Variant
Dim lngDate As Long

varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy", "Date for Filtering", Type:=2)
If varAns = False Then Exit Sub
lngDate = CLng(DateValue(varAns))

With ActiveSheet
  .Range("A1").AutoFilter Field:=7, Criteria1:=">" & lngDate, Operator:=xlAnd
  .UsedRange.SpecialCells(xlCellTypeVisible).Copy
End With
Workbooks.Add (xlWBATWorksheet)
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Ciao,
Holger
 
Upvote 0
HaHoBe said:
Hi,

why not save the date you asked for in a variable and use that in the new workbook with a reversed filter to delete all those records that do not fit. Another way is to add an empty workbook and copy/paste the visible range there.

First one may look like this where ActiveSheet is copied, original stays in workbook:

VBA Code: 
Public Sub MrE_1228116_1701A13()
' https://www.mrexcel.com/board/threads/vba-greater-than-date-issue-with-autofilter-on-a-new-workbook.1228116/
'copy Activesheet to new workbook, delete non-fitting data
Dim varAns As Variant
Dim lngDate As Long

varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy", "Date for Filtering", Type:=2)
If varAns = False Then Exit Sub
lngDate = CLng(DateValue(varAns))

ActiveSheet.Range("A1").AutoFilter Field:=7, Criteria1:=">" & lngDate, Operator:=xlAnd

'Move to new workbook
'Activesheet.Move
ActiveSheet.Copy

With ActiveSheet
  .Range("A1").AutoFilter Field:=7, Criteria1:="<=" & lngDate, Operator:=xlAnd
  .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  .Range("A1").AutoFilter
End With

End Sub

VBA Code: 
Public Sub MrE_1228116_1701A13_2()
' https://www.mrexcel.com/board/threads/vba-greater-than-date-issue-with-autofilter-on-a-new-workbook.1228116/
'copy visible range to new workbook
Dim varAns As Variant
Dim lngDate As Long

varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy", "Date for Filtering", Type:=2)
If varAns = False Then Exit Sub
lngDate = CLng(DateValue(varAns))

With ActiveSheet
  .Range("A1").AutoFilter Field:=7, Criteria1:=">" & lngDate, Operator:=xlAnd
  .UsedRange.SpecialCells(xlCellTypeVisible).Copy
End With
Workbooks.Add (xlWBATWorksheet)
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Ciao,
Holger
Click to expand...

Hello,
Thanks for this. I have tested both options and I get a prompt message to say that there is a bug in the code line : lngDate = CLng(DateValue(varAns)) . How do I fix this? I agree with your comment about deleting all of the rows before the Greater than Date. That is good advice. :)
Best regards
 
Upvote 0
Hi,

due to my local settings the date for today is displayed as 27.01.2023. I tested with swapping days and month but code accepted this as well. Maybe use this codeline instead which gives an example not only with literals but with a real date as well:

VBA Code: 
varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy, i.e. " & Format(DateSerial(2019, 12, 31), "dd.mm.yyyy"), "Date for Filtering", Type:=2)

You would need to pass the information what varAns holds in order to fix the code and the error number raised.

Holger
 
Upvote 0
HaHoBe said:
Hi,

due to my local settings the date for today is displayed as 27.01.2023. I tested with swapping days and month but code accepted this as well. Maybe use this codeline instead which gives an example not only with literals but with a real date as well:

VBA Code: 
varAns = Application.InputBox("Date greater than in Format dd.mm.yyyy, i.e. " & Format(DateSerial(2019, 12, 31), "dd.mm.yyyy"), "Date for Filtering", Type:=2)

You would need to pass the information what varAns holds in order to fix the code and the error number raised.

Holger
Click to expand...

Hi
I get raw data that comes with a date and time stamp. I would like to enter the date timestamp in the Inputbox.
The macro will delete rows before the Greater than date and then it will correct the format into dd.mm.yyyy. Does that make sense?
Regards
 
Upvote 0
Hi,

depending on whether it's text delievered or date. Sample looks like this (only posting part of the dataset for both):

MrE_1228116_1701A13_vba greater than dat_230126.xlsm
ABCDEFGHIJKLMN
1HeaderHeaderHeaderHeaderHeaderHeaderDate/TimestampHeaderHeaderHeaderHeaderHeaderHeaderHeader
2TestdataTestdataTestdataTestdataTestdataTestdata02.01.2020 06:33:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
3TestdataTestdataTestdataTestdataTestdataTestdata02.01.2020 17:06:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
4TestdataTestdataTestdataTestdataTestdataTestdata03.01.2020 03:39:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
5TestdataTestdataTestdataTestdataTestdataTestdata03.01.2020 14:12:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
6TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 00:45:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
7TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 11:18:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
8TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 21:51:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
9TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 08:24:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
10TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 18:57:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
11TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 05:30:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
12TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 16:03:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
13TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 02:36:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
14TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 13:09:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
Tabelle7


05.01.2020 was entered into Inputbox and result was

Tabelle2
ABCDEFGHIJKLMN
1HeaderHeaderHeaderHeaderHeaderHeaderDate/TimestampHeaderHeaderHeaderHeaderHeaderHeaderHeader
2TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 08:24:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
3TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 18:57:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
4TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 05:30:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
5TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 16:03:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
6TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 02:36:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
7TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 13:09:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
8TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 23:42:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
Tabelle1


Excel recognizes data in Column G as Date and Time.

Holger
 
Upvote 0
HaHoBe said:
Hi,

depending on whether it's text delievered or date. Sample looks like this (only posting part of the dataset for both):

MrE_1228116_1701A13_vba greater than dat_230126.xlsm
ABCDEFGHIJKLMN
1HeaderHeaderHeaderHeaderHeaderHeaderDate/TimestampHeaderHeaderHeaderHeaderHeaderHeaderHeader
2TestdataTestdataTestdataTestdataTestdataTestdata02.01.2020 06:33:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
3TestdataTestdataTestdataTestdataTestdataTestdata02.01.2020 17:06:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
4TestdataTestdataTestdataTestdataTestdataTestdata03.01.2020 03:39:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
5TestdataTestdataTestdataTestdataTestdataTestdata03.01.2020 14:12:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
6TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 00:45:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
7TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 11:18:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
8TestdataTestdataTestdataTestdataTestdataTestdata04.01.2020 21:51:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
9TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 08:24:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
10TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 18:57:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
11TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 05:30:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
12TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 16:03:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
13TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 02:36:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
14TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 13:09:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
Tabelle7


05.01.2020 was entered into Inputbox and result was

Tabelle2
ABCDEFGHIJKLMN
1HeaderHeaderHeaderHeaderHeaderHeaderDate/TimestampHeaderHeaderHeaderHeaderHeaderHeaderHeader
2TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 08:24:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
3TestdataTestdataTestdataTestdataTestdataTestdata05.01.2020 18:57:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
4TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 05:30:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
5TestdataTestdataTestdataTestdataTestdataTestdata06.01.2020 16:03:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
6TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 02:36:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
7TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 13:09:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
8TestdataTestdataTestdataTestdataTestdataTestdata07.01.2020 23:42:00TestdataTestdataTestdataTestdataTestdataTestdataTestdata
Tabelle1


Excel recognizes data in Column G as Date and Time.

Holger
Click to expand...

Hi

Please find attached. I still get a bug in relation to this codeline ' lngDate = CLng(DateValue(varAns))'
I copied your test in excel and then the macro pick up a bug.
Regards
 

Attachments

  • Capture2.PNG
    Capture2.PNG
    27 KB · Views: 10
  • Capture1.PNG
    Capture1.PNG
    30.3 KB · Views: 9
Upvote 0
The macro did not raise an error because of the data set but because of what you have not mentioned yet: what has been entered into the InputBox? Tell us what the variable varAns holds when the error is raised.

Holger
 
Upvote 0
HaHoBe said:
The macro did not raise an error because of the data set but because of what you have not mentioned yet: what has been entered into the InputBox? Tell us what the variable varAns holds when the error is raised.

Holger
Click to expand...

I enter this 09.01.2020 in the InputBox. The variable varAns is for the inputbox that has two options either be dd.mm.yyyy or yyyy.mm.dd? I am not sure date for filtering works and then Type:=2?
 
Upvote 0
Hi vbanewbie68,

my last try here, use this shortened codeline:

VBA Code: 
lngDate = CDate(varAns)

And to be honest: I would never let any user enter a date via Inputbox/Application.InputBox. By setting Type:=2 for the second one the value returned is a string but that string could hold any value or special character breaking the conversion to Date.

Microsoft had a Date Picker which was taken out of the available controls nearly ten years ago. I would either depend on any third party (free9 DatePicker/calnedarControl or help myself with a small UserForm (3 Comboboxes and 2 CommandButtons) but would have to make sure that Feb. 29, 2023 is not a valid Date (you can restrict that by making the controls available only after year and then month have been chosen).

Holger
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
411
Peter_SSs
Peter_SSs
U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
63
user29383902
U
S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
295
SPS41
S
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
641
Amo840812
A
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
990
Alex Blakenburg
A

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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
Back
Top