Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
markx
 
Posts: n/a
Default Consolidate different sheets to different workbooks

Hello everybody!
Could you help me pls on this:

In my particular exemple, I have like 34 workbooks (put together in the same
folder), every workbook having the same structu
- 6 different tabs AA, BB, CC, DD, EE, FF
- in every workbook the tabs have the same name (i.e. in workbook 1 we have
worksheets AA, BB, CC, DD, EE, FF, in workbook 2 it's the same and so
on...). The only thing that changes is the name of the workbooks and, of
course, the content of the sheets.

I would like to consolidate this in such way, that I would receive at the
end 6 different workbooks, and every workbook would contain 34 worksheets of
the same family (i.e. name).
Ideally (but it's not necessary if too complicated) the names of the new
created files would be like those of the "initial" sheets (i.e. AA, BB, CC,
DD, EE, FF), and the sheets names in these new files would be like the names
of the "initial" files from which they were taken.

So, just to recapitulate: instead of having X workbooks with Y different
worksheets each (same pattern for every workbook), I would like to have Y
workbooks with X worksheets each.

Is this feasible? Do you know any macro capable of doing this?
Pls advise,

Regards,
Mark


  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Mark,

Try the macro below.

Put the 34 files into the same folder, with no other files, and then change
the path name in the line:

.LookIn = "C:\Excel\Combine Folder"

to that folder.

HTH,
Bernie
MS Excel MVP

Sub RecombineSheets()
Dim myBook As Workbook
Dim mySht As Worksheet
Dim bookName As String
Dim shtName As String
Dim i As Integer

Application.DisplayAlerts = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\Excel\Combine Folder"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
Set myBook = Workbooks.Open(.FoundFiles(1))
shtName = Replace(myBook.Name, ".xls", "")
For Each mySht In myBook.Worksheets
bookName = mySht.Name
mySht.Name = shtName
mySht.Copy
ActiveWorkbook.SaveAs Filename:= _
.LookIn & "\" & bookName & ".xls", _
FileFormat:=xlNormal
mySht.Name = bookName
Next mySht
myBook.Close False

For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
shtName = Replace(myBook.Name, ".xls", "")
For Each mySht In myBook.Worksheets
bookName = mySht.Name
mySht.Name = shtName
mySht.Copy Befo=Workbooks(bookName & ".xls").Sheets(1)
Workbooks(bookName & ".xls").Save
mySht.Name = bookName
Next mySht
myBook.Close False
Next i
End If
End With

Application.DisplayAlerts = True

End Sub


"markx" wrote in message
...
Hello everybody!
Could you help me pls on this:

In my particular exemple, I have like 34 workbooks (put together in the

same
folder), every workbook having the same structu
- 6 different tabs AA, BB, CC, DD, EE, FF
- in every workbook the tabs have the same name (i.e. in workbook 1 we

have
worksheets AA, BB, CC, DD, EE, FF, in workbook 2 it's the same and so
on...). The only thing that changes is the name of the workbooks and, of
course, the content of the sheets.

I would like to consolidate this in such way, that I would receive at the
end 6 different workbooks, and every workbook would contain 34 worksheets

of
the same family (i.e. name).
Ideally (but it's not necessary if too complicated) the names of the new
created files would be like those of the "initial" sheets (i.e. AA, BB,

CC,
DD, EE, FF), and the sheets names in these new files would be like the

names
of the "initial" files from which they were taken.

So, just to recapitulate: instead of having X workbooks with Y different
worksheets each (same pattern for every workbook), I would like to have Y
workbooks with X worksheets each.

Is this feasible? Do you know any macro capable of doing this?
Pls advise,

Regards,
Mark




  #3   Report Post  
markx
 
Posts: n/a
Default

Thanks Bernie,
It's more than perfect!
Mark

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mark,

Try the macro below.

Put the 34 files into the same folder, with no other files, and then
change
the path name in the line:

.LookIn = "C:\Excel\Combine Folder"

to that folder.

HTH,
Bernie
MS Excel MVP

Sub RecombineSheets()
Dim myBook As Workbook
Dim mySht As Worksheet
Dim bookName As String
Dim shtName As String
Dim i As Integer

Application.DisplayAlerts = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\Excel\Combine Folder"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
Set myBook = Workbooks.Open(.FoundFiles(1))
shtName = Replace(myBook.Name, ".xls", "")
For Each mySht In myBook.Worksheets
bookName = mySht.Name
mySht.Name = shtName
mySht.Copy
ActiveWorkbook.SaveAs Filename:= _
.LookIn & "\" & bookName & ".xls", _
FileFormat:=xlNormal
mySht.Name = bookName
Next mySht
myBook.Close False

For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
shtName = Replace(myBook.Name, ".xls", "")
For Each mySht In myBook.Worksheets
bookName = mySht.Name
mySht.Name = shtName
mySht.Copy Befo=Workbooks(bookName & ".xls").Sheets(1)
Workbooks(bookName & ".xls").Save
mySht.Name = bookName
Next mySht
myBook.Close False
Next i
End If
End With

Application.DisplayAlerts = True

End Sub


"markx" wrote in message
...
Hello everybody!
Could you help me pls on this:

In my particular exemple, I have like 34 workbooks (put together in the

same
folder), every workbook having the same structu
- 6 different tabs AA, BB, CC, DD, EE, FF
- in every workbook the tabs have the same name (i.e. in workbook 1 we

have
worksheets AA, BB, CC, DD, EE, FF, in workbook 2 it's the same and so
on...). The only thing that changes is the name of the workbooks and, of
course, the content of the sheets.

I would like to consolidate this in such way, that I would receive at the
end 6 different workbooks, and every workbook would contain 34 worksheets

of
the same family (i.e. name).
Ideally (but it's not necessary if too complicated) the names of the new
created files would be like those of the "initial" sheets (i.e. AA, BB,

CC,
DD, EE, FF), and the sheets names in these new files would be like the

names
of the "initial" files from which they were taken.

So, just to recapitulate: instead of having X workbooks with Y different
worksheets each (same pattern for every workbook), I would like to have Y
workbooks with X worksheets each.

Is this feasible? Do you know any macro capable of doing this?
Pls advise,

Regards,
Mark






  #4   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Mark,

It's more than perfect!


That's a first for me!

Thanks for letting me know that you're happy with the macro. We always
appreciate feedback - well, positive feedback, at least ;-)

Bernie
MS Excel MVP


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Numbering sheets in workbook Red Excel Worksheet Functions 3 March 2nd 05 09:10 PM
Automatically copy selective sheets from one workbook to another Ann Excel Discussion (Misc queries) 0 March 1st 05 08:09 PM
How to hyperlink from a workbook to sheets in another workbook? MJOHNSON Excel Worksheet Functions 0 February 17th 05 09:31 PM
Copy comments to several sheets in a workbook? jen_l_333 Excel Worksheet Functions 1 January 7th 05 11:30 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM


All times are GMT +1. The time now is 01:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"