Convert workbooks of worksheets into different order

[Davy]

Great work from Ron in reading his macro http://www.rondebruin.nl/copy6.htm! I found it so clean to create multiple workbooks based on worksheet of the original workbook.


Am I lucky enough if there is any enhanced version such that:
In an Input Folder, there are workbooks of:
workbook wb1 with worksheet sh1, sh2, sh3, ... , shN
workbook wb2 with worksheet sh1, sh2, sh3, ... , shN
workbook wb3 with worksheet sh1, sh2, sh3, ... , shN
...
workbook wbN with worksheet sh1, sh2, sh3, ... , shN

and then convert them into:
In an Output Folder, there are workbooks of:
workbook sh1 with worksheet wb1
(having content same as input workbook wb1 worksheet sh1)
with worksheet wb2
(having content same as input workbook wb2 worksheet sh1)
with worksheet wb3
(having content same as input workbook wb3 worksheet sh1)
...
with worksheet wbN
(having content same as input workbook wbN worksheet sh1)
similarly, there are other workbooks of:
workbook sh2 with worksheet wb1, wb2, wb3, ... , wbN
workbook sh3 with worksheet wb1, wb2, wb3, ... , wbN
...
workbook shN with worksheet wb1, wb2, wb3, ... , wbN


Is it too complicated to convert?

[Ron de Bruin]

Hi Davy

So you want from all workbooks in the folder :
new workbook with all "Sheet1"
new workbook with all "Sheet2"
new workbook with all "Sheet3"
........................................


Do you have names of the sheets or can we use the index

Sheets(1)
Sheets(2)
..........


How many sheets you have in the workbooks ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Davy" <[email protected]> wrote in message
news:[email protected]...
>
> Great work from Ron in reading his macro
> http://www.rondebruin.nl/copy6.htm! I found it so clean to create
> multiple workbooks based on worksheet of the original workbook.
>
>
> Am I lucky enough if there is any enhanced version such that:
> In an Input Folder, there are workbooks of:
> workbook wb1 with worksheet sh1, sh2, sh3, ... , shN
> workbook wb2 with worksheet sh1, sh2, sh3, ... , shN
> workbook wb3 with worksheet sh1, sh2, sh3, ... , shN
> ..
> workbook wbN with worksheet sh1, sh2, sh3, ... , shN
>
> and then convert them into:
> In an Output Folder, there are workbooks of:
> workbook sh1 with worksheet wb1
> (having content same as input workbook wb1
> worksheet sh1)
> with worksheet wb2
> (having content same as input workbook wb2
> worksheet sh1)
> with worksheet wb3
> (having content same as input workbook wb3
> worksheet sh1)
> ..
> with worksheet wbN
> (having content same as input workbook wbN
> worksheet sh1)
> similarly, there are other workbooks of:
> workbook sh2 with worksheet wb1, wb2, wb3, ... , wbN
> workbook sh3 with worksheet wb1, wb2, wb3, ... , wbN
> ..
> workbook shN with worksheet wb1, wb2, wb3, ... , wbN
>
>
> Is it too complicated to convert?
>
>
> --
> Davy
> ------------------------------------------------------------------------
> Davy's Profile: http://www.excelforum.com/member.php...o&userid=31799
> View this thread: http://www.excelforum.com/showthread...hreadid=515237
>

[Davy]

Hi Ron,

Amazing that you offer your help so promptly!

For my exercise, there are about 70 workbooks each with about 20 worksheets. The workbook names can be defined in a table or all files in a folder can be used (either approach is acceptable). Each worksheet is named and they are the same for all workbooks. The worksheet name can be defined in a table or by referring to a standardized template (either approach is acceptable).

I am going to convert it into 20 workbooks of 72 worksheets which:
- 70 worksheets use formula referring to the source file
(such that the converted file can be updated easily when any source file is amended)

- 1 worksheet named as "Consolidated" such that all numeric fields (as defined in a standard template) of all of the 70 converted worksheets in the workbook shall be summed. (for cell with formula, they shall be kept the same as the standard template such that they can be used to calculated some relevant ratio).

- 1 worksheet named as "Merged" such that value of the 70 converted worksheets shall be copied and pasted starting from the C column whereas column A and B shall be filled with the worksheet name and the line number in the converted worksheet


I understand that the above requirements may be too specific in comparing to your other clear and generic macros (which are powerful). Any tool to work for a generalized requirements shall help me greatly!


Thanks a Million!

[Ron de Bruin]

Hi

I see what I can do for you


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Davy" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron,
>
> Amazing that you offer your help so promptly!
>
> For my exercise, there are about 70 workbooks each with about 20
> worksheets. The workbook names can be defined in a table or all files
> in a folder can be used (either approach is acceptable). Each
> worksheet is named and they are the same for all workbooks. The
> worksheet name can be defined in a table or by referring to a
> standardized template (either approach is acceptable).
>
> I am going to convert it into 20 workbooks of 72 worksheets which:
> - 70 worksheets use formula referring to the source file
> (such that the converted file can be updated easily when any
> source file is amended)
>
> - 1 worksheet named as "Consolidated" such that all numeric
> fields (as defined in a standard template) of all of the 70 converted
> worksheets in the workbook shall be summed. (for cell with formula,
> they shall be kept the same as the standard template such that they can
> be used to calculated some relevant ratio).
>
> - 1 worksheet named as "Merged" such that value of the 70
> converted worksheets shall be copied and pasted starting from the C
> column whereas column A and B shall be filled with the worksheet name
> and the line number in the converted worksheet
>
>
> I understand that the above requirements may be too specific in
> comparing to your other clear and generic macros (which are powerful).
> Any tool to work for a generalized requirements shall help me greatly!
>
>
> Thanks a Million!
>
>
> --
> Davy
> ------------------------------------------------------------------------
> Davy's Profile: http://www.excelforum.com/member.php...o&userid=31799
> View this thread: http://www.excelforum.com/showthread...hreadid=515237
>

[Ron de Bruin]

OK

Here is a tester to get every first sheet of all workbooks in C:\Data
Test this and we can work on this one

Sub Example()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim DateString As String
Dim FolderName As String

DateString = Format(Now, "dd-mmm-yy hh-mm-ss")
FolderName = "C:\" & DateString
MkDir FolderName

'Fill in the path\folder where the files are
'on your machine : MyPath = "C:\Data" or on a network :
MyPath = "C:\Data"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp

Application.ScreenUpdating = False

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

Set basebook = Workbooks.Add(1)

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

mybook.Close savechanges:=False
Next Fnum
End If
basebook.SaveAs FolderName & "\Sheets(1).xls"
basebook.Close False

MsgBox "You can find the files in " & FolderName

CleanUp:
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
> Hi
>
> I see what I can do for you
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Davy" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Hi Ron,
>>
>> Amazing that you offer your help so promptly!
>>
>> For my exercise, there are about 70 workbooks each with about 20
>> worksheets. The workbook names can be defined in a table or all files
>> in a folder can be used (either approach is acceptable). Each
>> worksheet is named and they are the same for all workbooks. The
>> worksheet name can be defined in a table or by referring to a
>> standardized template (either approach is acceptable).
>>
>> I am going to convert it into 20 workbooks of 72 worksheets which:
>> - 70 worksheets use formula referring to the source file
>> (such that the converted file can be updated easily when any
>> source file is amended)
>>
>> - 1 worksheet named as "Consolidated" such that all numeric
>> fields (as defined in a standard template) of all of the 70 converted
>> worksheets in the workbook shall be summed. (for cell with formula,
>> they shall be kept the same as the standard template such that they can
>> be used to calculated some relevant ratio).
>>
>> - 1 worksheet named as "Merged" such that value of the 70
>> converted worksheets shall be copied and pasted starting from the C
>> column whereas column A and B shall be filled with the worksheet name
>> and the line number in the converted worksheet
>>
>>
>> I understand that the above requirements may be too specific in
>> comparing to your other clear and generic macros (which are powerful).
>> Any tool to work for a generalized requirements shall help me greatly!
>>
>>
>> Thanks a Million!
>>
>>
>> --
>> Davy
>> ------------------------------------------------------------------------
>> Davy's Profile: http://www.excelforum.com/member.php...o&userid=31799
>> View this thread: http://www.excelforum.com/showthread...hreadid=515237
>>
>
>

[Davy]

Ron,

Thanks for the suggestion.
I tested your macro finding the following issues:

1. each input workbook has many hyperlink to other files. The system prompt for updating the link or not (for which I need to say NO for about 70 times in processing the 70 workbooks).
==> Any suggestion?

2. in each copied worksheet, they are pasted with the original formula. The case is that ech workbook was prepared by different people and send to someone for consolidation. Then, the original formula has no furhter meaning but I shall expect that for original wb1 and sh2 at cell A1, after conversion, in workbook sh2 worksheet wb1 at cell A1, it has formula [wb1.xls]sh2!A1.
It is a bit strange but it really helps such that for any further correction, the user can work on the original workbook only. Then, all of the converted workbooks can be updated easily. I have tested for performance of this concept which is acceptable.
==> how to do that?

3. I happens that one of the 70 workbooks are found of different purpose without the specific worksheet which aborts the macro.
==> how to ignore it and skip to the next workbook?



I am going to customize your macro in my exercise.


Can you help for the generation of worksheets "Merged" and "Consolidated"?
In generating worksheet "Consolidated", we can assume that every converted worksheets in the workbook are of the same format. The standard format for all worksheets can be referred to the workbook Standard.xls[sh1],[sh2], [sh3], ... [shN].

[Davy]

Hi Ron,

I resolved my mentioned issue 1 and working on issue 2.

Relating to issue 3, I need your suggestion for the followings:

Issue 4 - how to check if a worksheet of a specific name exists, if not, create a new worksheet with the name. (By knowing that, I can simply skip the creation of new worksheet in handling issue 3.)

Any suggestion for the Merging and Consolidation?

[Ron de Bruin]

Hi Davy

I have not much time now but I reply to you tomorrow after work



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Davy" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron,
>
> I resolved my mentioned issue 1 and working on issue 2.
>
> Relating to issue 3, I need your suggestion for the followings:
>
> Issue 4 - how to check if a worksheet of a specific name exists, if
> not, create a new worksheet with the name. (By knowing that, I can
> simply skip the creation of new worksheet in handling issue 3.)
>
> Any suggestion for the Merging and Consolidation?
>
>
> --
> Davy
> ------------------------------------------------------------------------
> Davy's Profile: http://www.excelforum.com/member.php...o&userid=31799
> View this thread: http://www.excelforum.com/showthread...hreadid=515237
>

[Ron de Bruin]

Hi Davy

You can use this function


If SheetExists("yoursheet") = False Then .....................


Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function



> Any suggestion for the Merging and Consolidation?

Look here
http://www.rondebruin.nl/copy2.htm
Or
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Davy" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Ron,
>
> I resolved my mentioned issue 1 and working on issue 2.
>
> Relating to issue 3, I need your suggestion for the followings:
>
> Issue 4 - how to check if a worksheet of a specific name exists, if
> not, create a new worksheet with the name. (By knowing that, I can
> simply skip the creation of new worksheet in handling issue 3.)
>
> Any suggestion for the Merging and Consolidation?
>
>
> --
> Davy
> ------------------------------------------------------------------------
> Davy's Profile: http://www.excelforum.com/member.php...o&userid=31799
> View this thread: http://www.excelforum.com/showthread...hreadid=515237
>

[Davy]

Thanks Ron!
With your guidance, I am almost there!

In working on the copying of worksheets, I finally hit the limit of 4000 different cell formats. It is strange to me as I used the same testing workbooks of multiple worksheets by giving different workbook names. I tried to save the output workbook and re-open it for every 10 processed input workbooks. Besides, I copied the format from a standard workbook to the copied worksheet. However, the problem is still there after processing 48 workbooks.

Finally, I got to process the 80 input workbooks (and applied the standard format) in two batches which is OK.
But I just wonder why the approach of close and re-open doesn't work for this case.

On the other hand, it applying the standard format for a worksheet which is highlighted before previous save. When the system try to copy its format, the system just hang there without response. I tried to select the cell A1 before the copy instruction, it doesn't work. Finally, I got to reset the highlight and save the standard format file, then, the copying of format can be done. The workaround is totally acceptable. but I just can't understand what happens.


Now, I can proceed to the Consolidation and Merging work which shall be manageable.


Thanks again!