Combine Sheets From Different Workbooks

[Guest]

Question from a newbie, sorry if this isn't the right place for it, I am not even sure if this can be done. Here is the situation
I am stuck in.
1. I have monthly filse that I update, and I do it all manually.
2. The problem is there are about 125 files each month that I update, and it takes a long time.
3. My files will be in different directories and each file is only one sheet.
4. I have one directory where all of the files are kept called History, the new files to be added are in a file called New
5. If the file in New is the same filename as one in Histroy, then I copy all the data out of New and add it to the
workbook of the same name in History

Is there a way to set up something to automatically take any workbook in the New folder and if there is a file of the same name in the History folder then copy all the data from the workbook in the New folder and add it to the data that already exists in the workbook in the History folder?

If there is a way to do this it would be great because right now this takes me about 6 to 10 hours to do, and I have to do it twice a month. I was told by a co-worker that a macro is what I need, but I have no idea about any of this stuff. Any assistance any of you can provide me on this would be greatly appreciated.

Thank you in advance for your time and consideration,

Tim Fortune

 

[Otto Moehrbach]

Tim
Yes.
You say each of the files consists of only one sheet. If the file name is
found in the History folder, do you want the data from the new file appended
below the existing data in that file? If so, what do you want to happen to
the new file (in the New folder) after the data is copied?
What do you want to happen if the file is not found in the History folder?
Move the file to the History folder?
What is the layout of your data? How many columns? Starting in which
column?

As an aside, you say that you have about 125 files to update every month. I
gather then that you have many more than 125 files in all. And you say that
they each file has only one sheet. Is there a reason why you don't
consolidate all the files into one file with 125++ sheets? Just curious.
It is much easier to handle one file with 125++ sheets than handle 125++
files with one sheet each.
Post back with answers to the above questions. Also include the full path
to both the History and the New folders. You can contact me direct via
email if you wish. If you'll be contacting me direct, include a small file
with one sheet of your "History" data and one sheet of your "New" data.
Please don't attach a file to a newsgroup posting. Remove "cobia97" from my
email address. HTH Otto

Question from a newbie, sorry if this isn't the right place for it, I am

not even sure if this can be done. Here is the situation

I am stuck in.
1. I have monthly filse that I update, and I do it all manually.
2. The problem is there are about 125 files each month that I update, and it takes a long time.
3. My files will be in different directories and each file is only one sheet.
4. I have one directory where all of the files are kept called

History, the new files to be added are in a file called New

5. If the file in New is the same filename as one in Histroy, then I

copy all the data out of New and add it to the

workbook of the same name in History

Is there a way to set up something to automatically take any workbook in

the New folder and if there is a file of the same name in the History folder
then copy all the data from the workbook in the New folder and add it to the
data that already exists in the workbook in the History folder?

If there is a way to do this it would be great because right now this

takes me about 6 to 10 hours to do, and I have to do it twice a month. I
was told by a co-worker that a macro is what I need, but I have no idea
about any of this stuff. Any assistance any of you can provide me on this
would be greatly appreciated.

 

[Ron de Bruin]

Hi Tim

If the file in New is the same filename as one in Histroy

You can't open two files with the same name to do the copy

Are the names exactly the same


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

Question from a newbie, sorry if this isn't the right place for it, I am not even sure if this can be done. Here is the situation
I am stuck in.
1. I have monthly filse that I update, and I do it all manually.
2. The problem is there are about 125 files each month that I update, and it takes a long time.
3. My files will be in different directories and each file is only one sheet.
4. I have one directory where all of the files are kept called History, the new files to be added are in a file called New
5. If the file in New is the same filename as one in Histroy, then I copy all the data out of New and add it to the
workbook of the same name in History

Is there a way to set up something to automatically take any workbook in the New folder and if there is a file of the same

name in the History folder then copy all the data from the workbook in the New folder and add it to the data that already exists
in the workbook in the History folder?

If there is a way to do this it would be great because right now this takes me about 6 to 10 hours to do, and I have to do it

twice a month. I was told by a co-worker that a macro is what I need, but I have no idea about any of this stuff. Any
assistance any of you can provide me on this would be greatly appreciated.

 

[Guest]

I will try and answer all your questions as well as I can.

1. If the file name is found in the History folder, do you want the data from the
new file appended below the existing data in that file?

Yes, if the workbook is located in the History folder and a woorkbook of the same
name is located in the New folder, then I have to take all the data in the woorkbook that
is in the New folder and add it to the data that is in the woorkbook in the History folder. It
will need to be appended to the end of whatever data already exists in the wookbook in the
History folder. Sorry this explination is a little confusing, even to me.

2. If so, what do you want to happen to the new file (in the New folder)
after the data is copied?

If it is possible I would like to have it delete the workbook in the New folder after it takes the
data and adds it to the workbook in the History folder. If this is not possible that is ok, right now
I just go in after I am done and delete all the woorkbooks in the New folder, but if it can
automatically delete them that would save me a step which would be great.

3. What do you want to happen if the file is not found in the History folder?
Move the file to the History folder?

Absolutely correct. If the workbook in the New folder does not already exist in the History folder then I
just add that workbook to the History folder from the New folder, because it is a new data layout and
will probably be updated in the future as well.

4. What is the layout of your data? How many columns? Starting in which
column?

All the data in all the workbooks always starts in the "A" column and starts on row "1". But the total
number of rows used and the total number of columns used changes somewhat from workbook to workbook.
The longest it will probably ever be rows wise is about "300", columns wise it will never go past about "S". I
hope this is exact enouph to still set up something, but that is something you will have to tell me.


5. As for the one file with multipe worksheets. The only reason there is that each workbook contains
certain data and gets sent to people to use, but some of the workbooks contain personal information
and sensitive data that is on a, how do you say it, "need to know basis" - so it works to have seperate
workbooks to send to different people.

6. And last but not least, directory names are. C:\Documents and Settings\Tim Fortune\Data Files\History
and C:\Documents and Settings\Tim Fortune\Data Files\New

I hope this helps. If there is anything else you need to know, or if I didn't answer the question completely, just post back
or email me and let me know. I will also copy this and send it to your email asap. Thanks for all of your time and consideration, and any assistance you can give me on this.

Tim Fortune

 

[Guest]

Tim, I have written several macros that do this very thing. My girlfriend is a sales coordinator, and used to have to take a zillion workbooks and combine them etc. It would take hours to do it all and then prone to errors etc. I don't want to spend time with this if others are writing something for you, but I would be willing to help you if you wanted to contact me directly. Take out the X's and ths stuff between the X's in the shown email address.