Transfer Copy/paste based on criteria

**chris100** · 01-13-2010, 08:21 PM

Hi all,

Way too long since i've been on this site. Got a question in VBA. Could someone help please with the following scenario:

6 worksheets ("Monday" ,"Tuesday" etc)
1 Worksheet (call it Main)

In the Main sheet let's say there are 3 column headings - Date, Inv No, Amount

I want to copy from the Main sheet to the appropriate day e.g if the dates in the main are13/01/10 and 14/01/10 then all the info pertaining to 13/01/10 should go to the Wednesday worksheet.

Sorry if i didn't explain myself properly - its getting late here! As usual many thanks to all you Excel guys and gals who have helped in the past.

Regards,

Chris

**Richard Buttrey** · 01-13-2010, 08:39 PM

Hi,

One way would be to add a fourth helper column to the main sheet and add the function:

Please Login or Register  to view this content.

and copy this down your data. This will return 1,2,3 etc. for Sunday, Monday, Tuesday

Now all you need do is construct a simple VBA looping macro which filters your main sheet data for each day, and then copies and pastes the filtered rows to the appropriate sheet.

HTH

**chris100** · 01-13-2010, 08:51 PM

Thanks Richard,

I have done similar to the first part - it was the VBA looping macro thing that i'm a complete novice at!

regards

**Richard Buttrey** · 01-13-2010, 09:29 PM

Hi,

Try the attached. Hopefully it gives you the general idea. Click the blue button to run the macro.

Regards

**chris100** · 01-14-2010, 07:37 PM

Thanks Richard - that was really handy. How can i stop it that if i enter different data afterwards the macro will append to the correct day rather than wipe over the previous data?
i.e run the macro once then erase that info, reenter and run the macro again.

Regards

Chris

**chris100** · 01-14-2010, 07:55 PM

Richard,

Further to the above - could you please explain a little how the macro in this case works e.g what "Cells(2, 1)" relates to? I tried changing things a little to experiment and keep getting run time errors.

Cheers again!

**Richard Buttrey** · 01-15-2010, 05:44 AM

Hi Chris,

The line in question refers to the range name "crit". If you look at this range in the workbook you'll see it refers to H1:H2.

So the .Cells(2,1) simply refers to the cell in the 2nd row 1st column of this range, i.e. H2. The .Cells instruction is quite useful since it enables you to address a particular cell relative to another. So Range("D4").Cells(3,3) would refer to F6, and Range("D4").Cells(0,0) would refer to C3, and Range("D4").Cells(-2,3) would refer to F1.

In your macro we are putting the loop counter x in the second cell of the criteria range i.e. cell H2, and x in this case loops from 1 to 7 which represents the day of the week that we've identified in column D.

HTH

**Richard Buttrey** · 01-16-2010, 10:38 AM

Hi Chris,

I see I forgot to answer the second part of the question.
At the moment the macro uses Excel's standard Data filtering process. This means that the output range of the date being filtered is automatically cleared as part of the filtering process. In this case each day's tab is a separate output range hence they all get refreshed.

In order to add new records to each day then the simplest way would be to have a single dedicated output range to which each day's data is first filtered. Then have the macro copy this data below any previous data on the relevant day's tab.

If you want me to revise the macro then let me know.

Regards

**chris100** · 01-17-2010, 08:12 AM

Hi Richard,

To be honest that would be great - I have to build something relatively sharpish and anything that speeds it along would be a lifesaver!

Cheers,

Chris

**Richard Buttrey** · 01-17-2010, 07:29 PM

Hi,

See the revised workbook attached.

I've added an extra button which will erase all the data on all the seven daily sheets if that is required.

Regards

**chris100** · 01-18-2010, 02:30 AM

HI Richard,

Just had a look at the adapted code this morning and it works great. I'm just going to adapt it to my worksheet and i'll keep you posted later today.

br,

chris

**chris100** · 01-18-2010, 05:08 PM

Hi Richard thanks for all your help so far. Please could you help with one more thing? I'm having trouble relating the code to my worksheet.

If you see the attached (i've removed as much references and code as possible to minimise its size) and look at worksheet ACTISHEET. This is to be the front office tool for sales staff to dissect sales on. The summary info is at cells A502:I502. When the ticket is processed the range A478:I500 is sent to somewhere then the line A502:I502 needs to go to the appropriate day Monday to Saturday.

I was thinking about sending the summary to a single page for the whole week but I wanted to try and keep everything as similar to the original as possible.

There is likely to be several entry tickets for one page in case a ticket is held in 'limbo' but in general if the code works for line A502:I502 then i can duplicate for A528:I528 if necessary.

Sorry for being a bother but when it comes to arrays and looping i'm a bit of a dufuss!

Regards,

Chris

**JamesKender** · 02-06-2010, 02:43 AM

Hello there. Have you found a solution to yuour problem yet Chris? I have somthing similar that i'm trying to sort out but not like your attached. Let me know please.