copying specific data from one worksheet to another in certain cells

[kiwieur]

Hi Guys,

I hope that I can explain this properly

I have a worksheet (sheetData) that is populated once a month from an access query and it contains data for a particular customer, this sheet contains data for several delivery locations for this customer. Each row has a unique refernce number in column "A" and then 3 data columns in B to D

I then have other sheets for each of those delivery locations. on these sheets each row is for a specific product which has a unique reference number, I have to put 3 values for each month in columns, lets say a sheet is call (sheetLocationA)and the values for each month go into various cells i.e.


reference will be in cell B3
values for January will be in cells D3 to F3
values for February will be in cells H3 to J3
values for march will be in cells L3 to N3
and so on

I need to be able to loop through the reference column in
"sheetLocationA" and look in "sheetData" to see if there is a record for that reference, if so copy the data in "sheetA" cells B to D and paste them into the relevant columns in "sheetLocationA" i.e

if the month is january then the values would go in "sheetLocationA" cells D3 to F3 and so on

Obviously as the month changes I will need to define which cells the data should be pasted to using a varuable of some sort.

I hope I have explained myself clearly and would appreciate any help that someone could give me on this.

at the moment it is done manually every month and can take up to 4 hrs to copy all the data.

Regards

Paul

 

[SkipVought]

Hi,

This can be accomplished quite simply on the sheet, without VBA, using lookup functions (VLOOKUP or INDEX & MATCH). I do this every day, with data that query from Oracle, DB2, MS Access, Excel & Text files.

There is a benefit if you query your Access db using MS Query, as there is a property of the QueryTable in bData Range Properties[/b] that will Fill down formulas in columns adjacent to data, regardless if you have fewer or greater number of rows returned.

Please post in forum68, if you need help with lookup functions.

Skip,

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

 

[kiwieur]

Hi Skip,

Thanks for the info, i will look at that

Regards

Paul