VBA - selecting all rows that are 1 day after date

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I want to automate some of our production scheduling for future runs and am trying to create some code to select all rows with a date 1 day after today...or the 'today' based on system date.

I've gotten this far (see below), but I'm not well versed enough in VBA and could use some help. Also, this may be very wrong, so any pointers would be appreciated.
VBA Code:
Sub gather()

Dim TDate As String
Dim TomDate As Date

TDate = Date
TomDate = DateAdd("d", "1", "Date")

Workbooks.Open "book1.xls"

Sheets("Test").Select

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Can you post a copy of your data using XL2BB? Also, what do you want to do with the data once it is selected?
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Can you post a copy of your data using XL2BB? Also, what do you want to do with the data once it is selected?
When I've selected it, I'll copy and paste it to a different sheet that will then use my current code for filtering.

I'm just trying to find a way to copy a specific range of rows based on date (today + 1)
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Can you post a copy of your data using XL2BB?
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I did some leg work, found a thread and took some of the code from that. I also applied a filter for the date range I wanted which made it a lot easier to copy the data I wanted. Below is my final code to copy data from a date 1 more than today and paste it into a specific file of mine.

VBA Code:
Sub Gather()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

Workbooks.Open "Workbook2.xls"

Dim tbl As Range
Set tbl = Sheets("Generic 2021").AutoFilter.Range
Set tbl = tbl.Resize(tbl.Rows.Count - 1)
Set tbl = tbl.Offset(1)

Sheets("Generic 2021").Select

'Establish a filter for date, then copy and paste it into the desired location'
ActiveSheet.Range("$A$1:$K$3333").AutoFilter Field:=2, Operator:=xlFilterValues, Criteria2:=Array(2, Date + 1)
tbl.Copy Workbooks("Workbook1").Sheets("Sheet1").Range("A1")

Workbooks("Workbook2.xls").Close SaveChanges:=False

End Sub
 
Solution

Forum statistics

Threads
1,141,413
Messages
5,706,301
Members
421,441
Latest member
VapesRub

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