Merge muliple sheets in multiple workbook below each other.

[Yossy]

how do i Merge muliple sheets in multiple workbook (in same folder) below
each other. However each respective sheet should go to a new sheet. Note all
sheets in individual workbook are named same.

Please help me. all responses appreciated.

Thanks

 

[Jacques ALARDET]

Hello Yossy

The methode is :
1. Read the Folder and stocke the name workbook in a table T1 in memory
2. For each Workbook in the table T1 in memory
2a Open the workbook
2b Stocke the contain of the sheet in a other table T2 in memory
2c Close the workbook
3. Create a new workbook
4. Fill the sheet with the table T2

J a c q u e s

 

[Yossy]

Thanks for your help. I am new to macro and I have no idea what you have
explained below.

Please elaborate further. I looked into Ron's code
http://www.rondebruin.nl/copy3.htm but there is no detail info about copying
and seperating multiple worksheet in respective multiple workbook to another
worksheet

 

[Jacques ALARDET]

Hello Yossy

Sorry, If you begin in VBA, this example is a few complexe ... and my
english is very bad

For scan the folder the is :

Function MSOStockerFichiers(Chemin As String) As Integer
'_Stocker les fichiers d'un dossier
MSOStockerFichiers = 0
Erase TZ5_Fichier,
TZ5_nb = 0
If MonFSO.FolderExists(Chemin) = False Then GoTo E1
For Each ForObject In MonFSO.GetFolder(Chemin).Files
With ForObject
TZ5_nb = TZ5_nb + 1
TZ5_Fichier(TZ5_nb) = .Name
End With
Next
MSOStockerFichiers = TZ5_nb
FP: Exit Function
'=
E1: MsgBox "Dossier non trouvé " & Chemin: GoTo FP
End Function

For scan the workbook the code is :

'Consolider les fiches
For I = 1 To TZ5_nb
If TZ5_Fichier(I) = Empty Then GoTo F1
On Error GoTo E3
Workbooks.Open Filename:=O_Chemin & "\" & TZ5_Fichier(I), _
ReadOnly:=True, UpdateLinks:=0
Worksheets(1).Select
W_Pfin = MSOLfin(Cells(A_Ldeb - 1, 1))
For L = A_Ldeb To W_Pfin
T2_nbL = T2_nbL + 1
On Error Resume Next
For C = 1 To T2_nbC
T2_Val(T2_nbL, C) = Cells(L, C)
Next
On Error GoTo 0
Next
S1: On Error GoTo 0
ActiveWorkbook.Close SaveChanges:=False
F1: On Error GoTo 0
Next

For creat the result the code is :

'Actualiser la consolidation
L = A_Ldeb - 1
Worksheets(1).Select
For I = 1 To T2_nbL
L = L + 1
For C = 1 To T2_nbC
Cells(L, C) = T2_Val(I, C)
Next
Next
A_Fichier = A_Prefixe & "." & Format(Date, "yyyy-mm-dd") & R_Ext
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=O_Chemin & "\" &
A_Fichier
Application.DisplayAlerts = True

You modify this code for your object

J a c q u e s