A little hint. (Sorta' to cover up the fact that I don't have a *really* good
source to point you to.) If you record macros to do simple steps (i.e., one
macro for each of the instructions provided) you can cut/copy the pieces
together to do what you want done. So for example, if you record a macro of
you opening a workbook, you can review the code and get an understanding of
what is going on. If you record a macro of you copying one page to another
document then you can see that as well. There are a few steps in which that
methodology won't work, for example, creating something that will allow you
to identify one/all of the files that you want to include... Although, if
several documents are opened by using the Ctrl key then you will see the
resulting code there also.

Kinda' get the picture? I apologize for not being able to provide the time
necessary to describe the code more fully. I do know that if you also look
at the help that is provided when you are in VBA in regards to the functions
worksheet.name, workbook.name, and copy, you should be on the way to full
development.

I think that I also forgot to include the logic that either does not
overwrite an existing worksheet, or it updates it. (Depending on your
circumstances.)

You will learn a lot if you review the code that Excel creates and go
through the help files for anything you do not understand, or even if you do
understand it, it might help just to become more knowledgeable.

Best of Luck. :)

"Xluser@work" wrote:

Many thanks GB.

I understand exactly what you have suggested in laymans terms. My problem is
more to do with converting this into code. Can you suggest a starting point
to locate such code?

Once I see a piece of code I am fairly good at changing to suit my
particular needs but I am useless when it comes to writing from scratch!

Many thanks

"GB" wrote:

Yes it is possible to automate this.

You need to somehow determine the number of files that you will be opening.
Also, hopefully the cost centre code (A, B, & C) used on each worksheet tab
is the same or at least A in workbook 1 is equal to A in workbook 2, though
not necessary to be present in workbook 3.

If that is true, then you will use one "program" to control the processes
necessary to "transpose" your data.

Although you do not need to perform all of the following actions in the
sequence described, some common sense will be necessary.

Obtain list of files.

Open/Verify open the first file.
Go through each worksheet and gather the name(s) of the cost centres.
For each cost centre create a workbook.
Add/copy the sheet of this first file to the new workbook. (If cells in the
worksheet contain more than 255 characters, you will also need to then copy
the contents of the worksheet in book 1 to your new workbook.)
Rename the worksheet to indicate from which book it came.

Open/Verify opened book 2.
Collect the cost centre names.
If a workbook does not exist for one of the cost centres, then create it.
Add/copy the worksheet to the appropriate book (Either existing, or newly
created.)
Again deal with cells that have greater than 255 characters.
Rename the new worksheet to indicate from which book it came.

Repeat the above process until all workbooks have been read from, and all
new workbooks have been created.

After a little review of the above, it may make sense to simply add a
worksheet to the new workbook, name it as the workbook from which data will
be copied, then select all cells from the source worksheet and copy to the
destination worksheet. This will prevent dealing with an error that appears
in Excel when a worksheet is copied that has a cell with 255 characters.

"Xluser@work" wrote:

At present I have three excel files, say 1, 2 &3 each with a tab relating to
a cost centre, say A, B & C as follows:

1
A B C

2
A B C

3
A B C

What I would like to do is change the split so that I have a workbook for
each cost centre containing the sheet from each file such as:

A
1 2 3

B
1 2 3

C
1 2 3.

Please can you someone advise if there is an automated way I can do this or
else point me in the right direction? I think the hardest part might be to
match the right cost centre to each new sheet? Also the number of cost
centres varies over time.

Many thanks.