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.