Copier datatables Excel de plusieurs feuilles de travail en une seule feuille

EDITION MASSIVE:

Comme lors du dernier entretien avec Iain, les parameters corrects ont été définis. J'ai supprimé les derniers extraits de code car ils ne sont pas corrects. Si quelqu'un est toujours intéressé, vérifiez l'historique des modifications.

J'espère que c'est l'édition finale. 😉

Donc, les conditions requirejses sont les suivantes:

1. Nom du mois en feuille. Nous avons utilisé une boîte de saisie pour cela.
2. Nous vérifions le nombre de lignes. Il existe trois conditions: 157 rangs total, 41 rangs total, et tout le rest.

Le sous-programme suivant fera l'affaire.

 Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Sub BlackwoodTransfer () Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Dim Summ As Worksheet, Ws As Worksheet Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Dim ShName As Ssortingng Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Dim nRow As Long Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Set Summ = ThisWorkbook.Sheets ("Résumé") Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub ShName = InputBox ("Entrez le mois pour Call Flow en mmmm (c'est-à-dire novembre, etc.):") & "Call Flow" Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub 'Renvoie le stream d'appels de novembre. Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Cela signifie qu'il ciblera chaque feuille qui contient November Call Flow dans son nom. Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Application.ScreenUpdating = False Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Pour chaque Ws dans ThisWorkbook.Worksheets Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Si InStr (1, Ws.Name, ShName)> 0 puis Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub 'À partir du premier caractère du nom de la feuille, s'il a novembre, alors ... Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub nRow = Summ.Cells (Rows.Count, 1) .End (xlUp) .Row + 1 Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub '... obtenez la prochaine ligne vide de la fiche récapitulative ... Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Sélectionnez Case Ws.Cells (Rows.Count, 1) .End (xlUp) .Row Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub '... vérifiez combien de lignes cette fiche qualifiée a ... Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Cas 157 Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub '... s'il y a 157 rangs au total ... Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Ws.Range (Cellules (57,1), Cellules (104, 13)). Copie Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub '... copy les lignes 57 à 104, 13 colonnes de large ... Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Summ.Range ("A" & nRow) .PasteSpecial xlPasteAll Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub '... et collez à la prochaine ligne vide dans la fiche récapitulative. Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Cas 41 Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Ws.Range (Cellules (23,1), Cellules (126, 13)). Copie Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Summ.Range ("A" & nRow) .PasteSpecial xlPasteAll Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Autre cas Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Ws.Range (Cellules (23,1), Cellules (30, 13)). Copie Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Summ.Range ("A" & nRow) .PasteSpecial xlPasteAll Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Fin Choisir Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Next Ws Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub Application.ScreenUpdating = True Sub BlackwoodTransfer() Dim Summ As Worksheet, Ws As Worksheet Dim ShName As Ssortingng Dim nRow As Long Set Summ = ThisWorkbook.Sheets("Summary") ShName = InputBox("Enter month for Call Flow in mmmm format (ie. November, etc.):") & " Call Flow" 'Returns November Call Flow. This means it will target every sheet that has November Call Flow in its name. Application.ScreenUpdating = False For Each Ws In ThisWorkbook.Worksheets If InStr(1, Ws.Name, ShName) > 0 Then 'Starting from first character of the sheet's name, if it has November, then... nRow = Summ.Cells(Rows.Count, 1).End(xlUp).Row + 1 '... get the next empty row of the Summary sheet... Select Case Ws.Cells(Rows.Count, 1).End(xlUp).Row '... check how many rows this qualified sheet has... Case 157 '... if there are 157 rows total... Ws.Range(Cells(57,1),Cells(104,13)).Copy '... copy Rows 57 to 104, 13 columns wide... Summ.Range("A" & nRow).PasteSpecial xlPasteAll '... and paste to next empty row in Summary sheet. Case 41 Ws.Range(Cells(23,1),Cells(126,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll Case Else Ws.Range(Cells(23,1),Cells(30,13)).Copy Summ.Range("A" & nRow).PasteSpecial xlPasteAll End Select End If Next Ws Application.ScreenUpdating = True End Sub 

@Iain: vérifiez les commentaires et les references croisées avec la database MSDN. Cela devrait expliquer ce que chaque fonction / méthode fait exactement. J'espère que cela t'aides!

 Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Sub CombineSheets () Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Dim ws As Worksheet, wsCombine As Worksheet Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Dim rg As Range Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Dim RowCombine As Integer Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Définissez wsCombine = ThisWorkbook.Worksheets.Add (ThisWorkbook.Worksheets (1)) Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub wsCombine.Name = "Combine" Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub RowCombine = 1 Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Pour chaque ws dans ThisWorkbook.Worksheets Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Si ws.Index <> 1 Then Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Définir rg = ws.Cells (1, 1) .CurrentRegion Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub rg.Copy wsCombine.Cells (RowCombine, 2) Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub wsCombine.Range (Cells (RowCombine, 1), Cells (RowCombine + rg.Rows.Count - 1, 1)) = ws.Name Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub RowCombine = RowCombine + rg.Rows.Count Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub wsCombine.Cells (1, 1) .EntireColumn.AutoFit Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Définir rg = Rien Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub Set wsCombine = Nothing Sub CombineSheets() Dim ws As Worksheet, wsCombine As Worksheet Dim rg As Range Dim RowCombine As Integer Set wsCombine = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1)) wsCombine.Name = "Combine" RowCombine = 1 For Each ws In ThisWorkbook.Worksheets If ws.Index <> 1 Then Set rg = ws.Cells(1, 1).CurrentRegion rg.Copy wsCombine.Cells(RowCombine, 2) wsCombine.Range(Cells(RowCombine, 1), Cells(RowCombine + rg.Rows.Count - 1, 1)) = ws.Name RowCombine = RowCombine + rg.Rows.Count End If Next wsCombine.Cells(1, 1).EntireColumn.AutoFit Set rg = Nothing Set wsCombine = Nothing End Sub 

Créez une feuille de calcul "Sommaire" qui doit contenir toutes datatables fusionnées. Ouvrez ThisWorkBook (appuyez simplement sur ALT + F11 dans votre classur Excel.Une nouvelle window s'ouvrira. Votre nom de la feuille de calcul sera visible sur le côté gauche. Continuez jusqu'à ce que vous voyiez ThisWorkBook) Double-click ThisWorkBook et ajoutez le code suivant:

 Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Sub SummurizeSheets () Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Dim ws As Worksheet Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Application.Screenupdating = False Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Feuilles ("Résumé"). Activer Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Pour chaque ws dans les feuilles de travail Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Si ws.Name <> "Résumé" Ensuite Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub ws.Range ("F46: O47"). Copier Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub ActiveSheet.Paste Range ("A65536"). End (xlUp) .Offset (1, 0) Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub Next ws Sub SummurizeSheets() Dim ws As Worksheet Application.Screenupdating = False Sheets("Summary").Activate For Each ws In Worksheets If ws.Name <> "Summary" Then ws.Range("F46:O47").Copy ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) End If Next ws End Sub 

Sub AddToMaster () 'cette macro entre dans le classur principal Dim wsMaster As Worksheet, wbDATA As Workbook Dim NextRow As Long, LastRow As Long Dim FileName comme Ssortingng Dim FolderPath As Ssortingng Dim n As Long Dim i

Définir wsMaster = ThisWorkbook.Sheets ("Sheet1")

'Spécifiez le path du dossier

FolderPath = "D: \ work \"

'spécifiant le nom du file

FileName = Dir (FolderPath & " .xls ")

Do While FileName <> ""

NextRow = wsMaster.Range ("A" et Rows.Count) .End (xlUp) .Row + 1

Définissez wbDATA = Workbooks.Open (FolderPath & FileName)

 With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With Avec wbDATA.Sheets ("product_details") With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With LastRow = .Range ("A" et .Rows.Count) .End (xlUp) .Row With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With 'Si LastRow> 5 Then With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With Pour i = 2 à LastRow With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With .Range ("A2: j" & i) .Copy With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With wsMaster.Range ("A" et NextRow) .PasteSpecial xlPasteValues With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With 'Set NextRow = NextRow With wbDATA.Sheets("product_details") LastRow = .Range("A" & .Rows.Count).End(xlUp).Row ' If LastRow > 5 Then For i = 2 To LastRow .Range("A2:j" & i).Copy wsMaster.Range("A" & NextRow).PasteSpecial xlPasteValues 'Set NextRow = NextRow Next i End With 

FileName = Dir () Loop

wbDATA.Close False End Sub