How to split and consolidate sheets into new workbooks

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.

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.

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.

This piece of code takes all the spreadsheets in a lower directory called SOURCE and creates all the new spreadsheets in a Directory that must exist called target

root - contains this code
root\source - contains source spreadsheets and nothing else
root\target - all new spreadsheets created here
Option Explicit

Sub reMix()
Dim strSourcePath As String
Dim strSource As String
Dim strTarget As String
Dim strFiles(1 To 50) As String
Dim iFiles As Integer
Dim i As Integer

Dim wkbSource As Workbook
Dim wkbTarget As Workbook

Dim wksSource As Worksheet
Dim wksTarget As Worksheet
Dim wks As Worksheet

Dim str As String

strSourcePath = ThisWorkbook.Path & "\source\"
strTarget = ThisWorkbook.Path & "\target\"

str = Dir(strSourcePath & "*.xls")
iFiles = 0
Do Until str = ""
iFiles = iFiles + 1
strFiles(iFiles) = str
str = Dir()
Loop


For i = 1 To iFiles
strSource = strFiles(i)
Set wkbSource = Workbooks.Open(strSourcePath & strSource)
For Each wks In wkbSource.Worksheets()
str = strTarget & wks.Name & ".xls"
If Dir(str) = "" Then
wks.Copy
Set wkbTarget = ActiveWorkbook
Set wksTarget = ActiveSheet
wkbTarget.SaveAs str
Else
Set wkbTarget = Workbooks().Open(str)
wks.Copy wkbTarget.Worksheets(1)
Set wksTarget = ActiveSheet
End If
wksTarget.Name = Left(wkbSource.Name, Len(wkbSource.Name) - 4)
Set wksTarget = Nothing

wkbTarget.Close xlYes
Set wkbTarget = Nothing
Next
wkbSource.Close xlNo
Set wkbSource = Nothing
Next
MsgBox "done"
End Sub


you should have error trapping etc

hope it helps

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.

A commented version

Option Explicit

Sub reMix()
Dim strSourcePath As String
Dim strSource As String
Dim strTarget As String
Dim strFiles(1 To 50) As String
Dim iFiles As Integer
Dim i As Integer

Dim wkbSource As Workbook
Dim wkbTarget As Workbook

Dim wksSource As Worksheet
Dim wksTarget As Worksheet
Dim wks As Worksheet

Dim str As String

'these define where the files are being taken from and being put
'it is assumed that:
' - all excel files in the source folder will be used
' - the target folder is empty
strSourcePath = ThisWorkbook.Path & "\source\"
strTarget = ThisWorkbook.Path & "\target\"

'create an arry of all the excel files
' have to do this as preprocess so that I can test for existence of
' the target file using the dir function
str = Dir(strSourcePath & "*.xls")
iFiles = 0
Do Until str = ""
iFiles = iFiles + 1
strFiles(iFiles) = str
str = Dir()
Loop


For i = 1 To iFiles 'loop through the source files
strSource = strFiles(i)
Set wkbSource = Workbooks.Open(strSourcePath & strSource)
For Each wks In wkbSource.Worksheets() 'loop through each worsheet in the source file
str = strTarget & wks.Name & ".xls" 'use the sheet name to create an output file name
If Dir(str) = "" Then 'see if the file exists. could do this with an error trap, but it is messier
wks.Copy 'create a new book
Set wkbTarget = ActiveWorkbook
Set wksTarget = ActiveSheet
wkbTarget.SaveAs str
Else
Set wkbTarget = Workbooks().Open(str) ' add sheet to old book
wks.Copy wkbTarget.Worksheets(1)
Set wksTarget = ActiveSheet
End If

'name sheet with workbook name
wksTarget.Name = Left(wkbSource.Name, Len(wkbSource.Name) - 4)
Set wksTarget = Nothing

wkbTarget.Close xlYes
Set wkbTarget = Nothing
Next
wkbSource.Close xlNo
Set wkbSource = Nothing
Next
MsgBox "done"
End Sub