VBA Code: Importing Data that is only in certain date ranges

[SkyeS]

Hey guys.

So I've been working on this the past two days and no one seems to have a good solution (that I found), but I'm sure there is one.

I have an excel document that contains a bunch of data but the first column contains a date for event.
I have code that pulls the data from that excel document over into a new sheet, but it will only pull whatever ranges i manually put in the code, but the person I'm working on this for is not going to be able to do that. I know there is a way to do it, but I'm just not sure the best way to do it. I've been playing around with while loops and if statements and just selecting and copying and pasting but I also just started learning VBA yesterday afternoon. Anyhow this is the code that I have that imports the data. I just need someone to help me filter it over so that only certain dates in the range of last week to the future are imported and have it so this is a dynamic thing so it takes today's date by using the NOW function and then subtracts 7 days and then all the days from Now-7 are imported over. I got part of it to work by using the following code:

Dim nowDate As Date, realDate As Date


nowDate = Now
realDate = DateAdd("d", -7, nowDate)


MsgBox realDate

But I can't make the connection between that and filtering it by that. So if someone could please help that would be wonderful! Here is my importing code:

Sub ImportData()


' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook


' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook


' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
customerFilename = Application.GetOpenFilename(filter, , caption)


Set customerWorkbook = Application.Workbooks.Open(customerFilename)


' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(2)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)


targetSheet.Range("A1", "H68").Value = sourceSheet.Range("A1", "H68").Value


' Close customer workbook
customerWorkbook.Close


End Sub

 

[JLGWhiz]

I am not clear on your date criteria. Do you want just the last seven days? Or are you saying you want anything starting seven days ago and into the future? Or are you saying you want anything older than seven days. Also, what column does the dates reside in?

 

[SkyeS]

Sorry. Yeah I want anything starting 7 days ago and into the future. And the dates are in column A. Thank you so much for all of your help!

 

[JLGWhiz]

This ran OK in a test set up for me. Give it a try.

Sub ImportData()
Dim customerBook As Workbook
Dim filter As String, dt As Date
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook, sourceSheet As Worksheet
Dim targetWorkbook As Workbook, targetSheet As Worksheet
Set targetWorkbook = ThisWorkbook
Set targetSheet = ThisWorkbook.Worksheets(2)
dt = Date - 7 'Get date for seven days back.
filter = "Text files(*.xlsx), *.xlsx"
caption = "Please Select an input file"
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
Set sourceSheet = customerWorkbook.Worksheets(1)
sourceSheet.Range("A1:H68").AutoFilter 1, ">" & dt 'Filter for all dates greater than seven days ago.
sourceSheet.Range("A1:H68").SpecialCells(xlCellTypeVisible).Copy targetSheet.Range("A1") 'Copy the filtered data to target workbook.
sourceSheet.AutoFilterMode = False 'turn of filter
customerWorkbook.Close False 'close source file
End Sub

 

[SkyeS]

Thank you so much!!! It works wonderfully! So sorry for the late responses since I was out of town for the weekend, but thank you so much!