Mac Forum / Applications / Excel / February 2005

OK, there must be something in the worksheet, then.

Do you think you could send a workbook over to me? I'd rather have a look at
it to find out what is preventing the macro to run. There *must* be
something!

I'll see how to try and adapt it. But that's not the difficult part about
it.

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

Anita :

I think I figured it out: I suppose you haven't copied the macro in a VBA
module added to your original workbook, but to another workbook. So, when it
comes to the
   For Each sh In ThisWorkbook.Worksheets
line, "ThisWorkbook" won't refer to your active Workbook (as you'd expect, I
suppose), but to the one to which the macro module belongs to.

If you insert a VBA module in your workbook and copy the macro on it, it
does work, as I could find out with the workbook you sent to me.

So, here's a new version, and it should merge all your 24 workbooks into
one, each sheet bearing the content of every sheet in one workbook. I still
have to test it to see if this does work well on the Mac (I'm working on a
Windows version here!). There's no need to add any "Master" sheet to your
workbooks any more. But there needs to be a "Master.xls" Workbook, containing
a "Template" Worksheet with the column headers I guess you'd like to keep.
I'll send you a copy of such a workbook later tonight, after having tested it
on the Mac. The macro will already be inside, of course.

Basically, you'll have to gather your 24 wokbooks into one Folder, in which
you'll have to put that "Master.xls" workbook too. When prompted to, you'll
browse to that "Master.xls" book, (in order to get the filepath to the
folder). And that should be all...

So here's the new set:

Public Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), Lookat:=xlPart, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Sub MergeBooks()
Dim sh As Worksheet
Dim last As Long, shLast As Long
Dim rng As Range
Dim MyPath As String, MyDir As String
MyPath = Application.GetOpenFilename _
(Title:="Where is your ""Master.xls"" file?", _
ButtonText:="Choose")
MyPath = Left(MyPath, Len(MyPath) - 10)
MyDir = Left(MyPath, Len(MyPath) - 1)
Dim F
With Application.FileSearch
.NewSearch
.LookIn = MyDir
.Execute
On Error Resume Next
For Each F In .FoundFiles
If F = MyPath & "Master.xls" Then GoTo TheNextBook
Mytab = Right(F, Len(F) - Len(MyPath))
If Right(Mytab, 4) = ".xls" Then Mytab = Left(Mytab, Len(Mytab) - 4)
Workbooks("Master").Activate
Sheets("Template").Copy before:=Worksheets("Template")
ActiveSheet.Name = Mytab
Workbooks.Open F
For Each sh In ActiveWorkbook.Worksheets
last = LastRow(Workbooks("Master.xls").Worksheets(Mytab))
shLast = LastRow(sh)
Set rng = Workbooks("Master.xls").Worksheets(Mytab).Cells(last + 1, 1)
sh.Range(sh.Rows(2), sh.Rows(shLast)).Copy Destination:=rng
Next sh
ActiveWorkbook.Close
TheNextBook:
Next F
End With
Worksheets("Template").Delete
End Sub

---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.