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

SkyeS

New Member
Joined
May 21, 2015
Messages
40
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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!
 
Upvote 0
This ran OK in a test set up for me. Give it a try.

Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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