VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

**drsadistic** · 08-08-2014, 04:10 PM

Here is another question for you geniuses here. I use my workbook to track sales data from one store to the next. I use my workbooks to compare data from year to year. Each year's data is displayed on a separate worksheet. '2013' has 2013's data, '2014' has 2014's. On the 2014 sheet, I have a Prior Year's Sales that pulls data from the previous year's spreadsheet using a formula which I just drag down each day I enter sales. I would like to automate this process and have the VBA code check for today's date and automatically pull the previous year's sales data from the '2013' sheet and put it in the appropriate cell on the '2014' worksheet. I hope I have explained this well enough to understand. I've included a link to my workbook for reference. Thank you in advance.

I had to use dropbox since I can't post a file over 1 MB. The file size is around 1.25 MB.

https://www.dropbox.com/s/w4g49ibab1...ortingWIP.xlsm

**mooglebagel** · 08-08-2014, 07:42 PM

Why don't you just pull the formula all the way down to the end of the year now and your data will be filled going forward?

**drsadistic** · 08-09-2014, 12:18 AM

Originally Posted by mooglebagel

Why don't you just pull the formula all the way down to the end of the year now and your data will be filled going forward?

I have a table in my workbook that gives me an overview of my sales progress that is accurate upto the day. That helps me to manage my staff and supply better. If I pull the formula all the way down then my data become inaccurate for the period of time the data range covers.

**mooglebagel** · 08-09-2014, 12:41 AM

What if you used the formula and copied down:

=IF(B226<TODAY(),'2013'!C225,0)
or
=IF(B226<=TODAY(),INDEX('2013'!$C$4:$C$375,MATCH(DATE(YEAR(B226)-1,MONTH(B226),DAY(B226)),'2013'!$B$4:$B$375,0)),0)

for example, in row 226. It would show zero values for dates after one year ago today and not affect any total calculations. Can code it in VBA but I'm not sure you need to.

**drsadistic** · 08-09-2014, 12:48 AM

You, my friend, are awesome! Thank you your first code was perfect. I don't know why I didn't think of that. Reputation added!

**mooglebagel** · 08-09-2014, 12:53 AM

Glad it works. Thanks!

VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

LinkBack

Thread Tools

Rate This Thread

Display

VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Re: VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Re: VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Re: VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Re: VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Re: VBA Code to Pull Data from a Sheet Based on Date and Add that Data to Another Sheet

Thread Information

Users Browsing this Thread

Similar Threads

Pull Data from Sheet Based on Criteria -- Populate UserForm & ask for Missing Data

Pull Data from Sheet Based on Criteria -- Populate UserForm & ask for Missing Data

How to pull data from one sheet into new sheet based on two criteria

Need Macro to pull data from one excel sheet to another based on a job# and comparing date

Macro code to pull data from different sheets based on the header of the main sheet

Bookmarks

Bookmarks

Posting Permissions