copy data from one workbook to another based on date criteria

[kiwieur]

Hi People,

I hope I can explain this properly so here goes

I am outputting some data from an access db into a workbook, this data is based on the previous days date. There are 13 columns and the first one contains the relevant date.

I then have another workbook that contains a sheet that holds all the data for a year, the first column contains the dates and the next 11 columns hold the data. what i would like to do is automate this so that when the data is outputted from access I can then copy the data into the relevant cells based on the date.

so basically what i am saying is that i would like to import the data from workbook 1 into workbook 2 by finding the correct row in workbook 2 based on the date and then pasting the data into columns 2 to 12.

the data in workbook 2 is used to create 12 charts that are then displayed on an internal intranet.

Is this possible and if so could someone put me on the right track to accomplish this please

Regards

Paul

 

[SkipVought]

Hi,

"finding the correct row in workbook 2 based on the date"

If your copy range has the date, just paste at the BOTTOM of your table in your second workbook. No need to FIND any date.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kiwieur]

Hi Skip,

sorry i have not replied sooner, I probably have not explained this properly so i'll try again

workbook 2 is formatted to hold data for a full year and is laid out as follows

Col A contains dates
Col B contains numbers reprenting the days i.e. 1 to 31
Col C to M contains values that at the moment are inputted manually

Rows 2 to 32 contains the dates for January 1st to January 31st

Row 33 contains formualas that sum the data in rows 2 to 32 for January Monthly figures

Rows 34 to 62 contains data for February 1st to Feruary 29th

Row 63 contains formualas that sum the data in rows 2 to 32 for February Monthly figures

etc, etc

all this data is then used to produce up to 12 charts

therefore just appending to the bottom of the sheet is not really an option.

This is why I would like to be able to find the relevant row in workbook 2 based on the date in workbook 1 and then copy all the data from workbook 1 into the correct row in workbook 2

I hope I have explained this better this time as I do appreciate your help

Regards

Paul

 

[SkipVought]

You really do not need the SUBTOTAL rows in your table. The Data > Subtotal feature does the same thing without having to mess with formulas, yet get the SAME results.

So, back to the original suggestion. Just paste the new month's data at the bottom and sort if necessary.

"all this data is then used to produce up to 12 charts"

I do something similar with ONE chart, that can display data for DOZENS of manufacturing cost centers and scores of machines, using Sheet Controls to FILTER the desired data to display the subset of interest for the user. In your case, it would be the YEAR & MONTH.

This approch is much more professional, AND is much simpler to maintain, as changes are made to the Chart (and CHANGES do occur!!!)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}