Copying data to other workbooks 1

[davedave24]

Hi, I have something simple to do (the code should be okay) but wondered what the best way would be.

There is a workbook with a sheet for each customer (there are about 38, but more could be added). When a record is entered, my form locates the customer's sheet, finds the appropriate section on that sheet (searches by Week Number), then inputs all my data under that.

Then, each customer has their own folder, with a seperate workbook for each week. Currently, at the end of the week, they just copy each customer's data from the past week into a workbook, save it as 'customer-week-42.xls'. (I know there are better ways, for example 1 workbook for each customer, with each week on a seperate sheet, but for now that doesn't matter.)

The data they copy is about 10-20 rows, by 20 columns.

I think there are a few ways to do this:

1) as each record is entered in the form and to the customer's 52 week sheet, their week is then copy/pasted over their weekly workbook.

2) at the end of the week, the user presses a button, which cycles through each customer, takes the current week number, and copies it into the weekly workbook.

3) as each record is entered in the form, it will first write to the customer's sheet on the 52 week page, then it will open (check if it exists first) that customer's weekly workbook, which will have the layout, and paste the data in the same manner.

I hope this makes sense.

simple diagram of worksheets

http://imgur.com/VgGIc.png

 

[SkipVought]

As I was going to St. Ives,

I met a man with seven wives.

Each wife had seven bags.

Each bag had seven cats.

Each cat had seven kits.

Kits, cats, bags, wives,

How many were going to St. Ives?

WHAT A MESS!

but wondered what the best way would be

ONE workbook, ONE sheet, multiple reports as required.


Skip,

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

 

[davedave24]

Yep. It's just how they've done it. They have, let's say 20 pieces of data, and enter it manually into about 6 different places, and want me to automate that (it takes about 10-15 minutes for them to do 1 at the moment). Bit of a clusterfuck.

 

[davedave24]

Oops pressed submit by accident.

The reason they copy it to a new workbook for each customer each week, is that they then send that customer a weekly report of their stuff.

 

[SkipVought]

"The reason they copy it to a new workbook for each customer each week, is that they then send that customer a weekly report of their stuff. "

That is simply a REPORT that you could extract from ONE SHEET, for ANY customer for ANY period, if you have properly configured your ONE TABLE.

Believe me, it would be soooooo much simpler to process and maintain!

Whoever designed your process, ought to be saddled with processing and maintaining this monstrosity, or drawn & quartered, whichever they prefer.

Skip,

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