Convert workbooks of worksheets into different order

[Davy]

Great work from Ron in reading his macr
http://www.rondebruin.nl/copy6.htm! I found it so clean to creat
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 wb
worksheet sh1)
with worksheet wb2
(having content same as input workbook wb
worksheet sh1)
with worksheet wb3
(having content same as input workbook wb
worksheet sh1)
...
with worksheet wbN
(having content same as input workbook wb
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]

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

 

[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

Hi

I see what I can do for you

 

[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

 

[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

 

[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!