Passer au contenu principal

Comment combiner plusieurs classeurs en un seul classeur principal dans Excel?

Avez-vous déjà été bloqué lorsque vous devez combiner plusieurs classeurs dans un classeur principal dans Excel? Le plus terrible est que les classeurs que vous devez combiner contiennent plusieurs feuilles de calcul. Et comment combiner uniquement les feuilles de calcul spécifiées de plusieurs classeurs en un seul classeur ? Ce didacticiel présente plusieurs méthodes utiles pour vous aider à résoudre le problème étape par étape.


Combinez plusieurs classeurs en un seul classeur avec la fonction Déplacer ou Copier

S'il n'y a que quelques classeurs à combiner, vous pouvez utiliser la commande Déplacer ou Copier pour déplacer ou copier manuellement des feuilles de calcul du classeur d'origine vers le classeur maître.

1. Ouvrez les classeurs que vous fusionnerez dans un classeur maître.

2. Sélectionnez les feuilles de calcul dans le classeur d'origine que vous allez déplacer ou copier dans le classeur maître.

Notes:

1). Vous pouvez sélectionner plusieurs feuilles de calcul non adjacentes en maintenant la touche Ctrl et cliquez sur les onglets de la feuille un par un.

2). Pour sélectionner plusieurs feuilles de calcul adjacentes, veuillez cliquer sur le premier onglet de la feuille, maintenez le bouton Shift puis cliquez sur le dernier onglet de la feuille pour les sélectionner tous.

3). Vous pouvez faire un clic droit sur n'importe quel onglet de feuille, cliquer sur Sélectionnez toutes les feuilles dans le menu contextuel pour sélectionner toutes les feuilles de calcul du classeur en même temps.

3. Après avoir sélectionné les feuilles de calcul nécessaires, cliquez avec le bouton droit sur l'onglet de la feuille, puis cliquez sur Déplacer ou copier dans le menu contextuel. Voir la capture d'écran:

4. Puis le Déplacer ou copier une boîte de dialogue apparaît, dans le Réserver dans la liste déroulante, sélectionnez le classeur principal dans lequel vous allez déplacer ou copier des feuilles de calcul. Sélectionnez déplacer pour terminer dans le Avant feuille case, cochez la case Créer une copie et enfin cliquez sur le OK .

Ensuite, vous pouvez voir les feuilles de calcul dans deux classeurs combinés en un. Veuillez répéter les étapes ci-dessus pour déplacer des feuilles de calcul d'autres classeurs vers le classeur maître.


Combinez plusieurs classeurs ou feuilles de classeurs spécifiées dans un classeur principal avec VBA

Si plusieurs classeurs doivent être fusionnés en un seul, vous pouvez appliquer les codes VBA suivants pour y parvenir rapidement. Veuillez faire comme suit.

1. Placez tous les classeurs que vous souhaitez combiner en un sous le même répertoire.

2. Lancez un Excel fichier (ce classeur sera le classeur maître).

3. appuie sur le autre + F11 clés pour ouvrir le Microsoft Visual Basic pour les applications fenêtre. dans le Microsoft Visual Basic pour les applications fenêtre, cliquez sur insérer > Module, puis copiez ci-dessous le code VBA dans la fenêtre Module.

Code VBA 1 : Fusionner plusieurs Excel classeurs en un seul

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub
	

Notes:

1. Le code VBA ci-dessus conservera les noms de feuille des classeurs d'origine après la fusion.

2. Si vous souhaitez distinguer quelles feuilles de calcul du classeur maître proviennent d'où après la fusion, veuillez appliquer le code VBA 2 ci-dessous.

3. Si vous souhaitez simplement combiner des feuilles de calcul spécifiées des classeurs dans un classeur maître, le code VBA 3 ci-dessous peut vous aider.

Dans les codes VBA, "C: \ Users \ DT168 \ Desktop \ KTE \»Est le chemin du dossier. Dans le code VBA 3, "Feuille1, Feuille3"correspond aux feuilles de calcul spécifiées des classeurs que vous combinerez en un classeur maître. Vous pouvez les modifier en fonction de vos besoins.

Code VBA 2: Fusionnez les classeurs en un seul (chaque feuille de calcul sera nommée avec le préfixe de son nom de fichier d'origine):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Code VBA 3: Fusionner les feuilles de calcul spécifiées des classeurs dans un classeur maître:

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

4. appuie sur le F5 clé pour exécuter le code. Ensuite, toutes les feuilles de calcul ou les feuilles de calcul spécifiées des classeurs dans le certain dossier sont combinées à un classeur principal à la fois.


Combinez facilement plusieurs classeurs ou feuilles de classeurs spécifiques dans un classeur

Heureusement, le Combiner utilitaire de classeur de Kutools for Excel facilite beaucoup la fusion de plusieurs classeurs en un seul. Voyons comment faire fonctionner cette fonction en combinant plusieurs classeurs.

Avant d'appliquer Kutools for Excel, S'il vous plaît téléchargez et installez-le d'abord.

1. Créez un nouveau classeur et cliquez sur Kutools "Plus" > Combiner. Ensuite, une boîte de dialogue apparaît pour vous rappeler que tous les classeurs combinés doivent être enregistrés et que la fonctionnalité ne peut pas être appliquée aux classeurs protégés, veuillez cliquer sur le OK .

2. dans le Combiner des feuilles de travail assistant, sélectionnez Combinez plusieurs feuilles de calcul de classeurs dans un seul classeur , puis cliquez sur le Suivant bouton. Voir la capture d'écran:

3. dans le Combiner des feuilles de travail - Étape 2 sur 3 boîte de dialogue, cliquez sur le Ajouter > Déposez le or Dossier ajouter le Excel fichiers que vous fusionnerez en un seul. Après avoir ajouté le Excel fichiers, cliquez sur le Finition et choisissez un dossier pour enregistrer le classeur maître. Voir la capture d'écran:

Désormais, tous les classeurs sont fusionnés en un seul.

Par rapport aux deux méthodes ci-dessus, Kutools for Excel présente les avantages suivants:

  • 1) Tous les classeurs et feuilles de calcul sont répertoriés dans la boîte de dialogue;
  • 2) Pour les feuilles de calcul que vous souhaitez exclure de la fusion, décochez-la simplement;
  • 3) Les feuilles de calcul vierges sont automatiquement exclues;
  • 4) Le nom de fichier d'origine sera ajouté comme préfixe au nom de la feuille après la fusion;
  • Pour plus de fonctions de cette fonctionnalité, veuillez visiter ici.

  Si vous souhaitez bénéficier d'un essai gratuit (30-jour) de cet utilitaire, veuillez cliquer pour le télécharger, puis passez à appliquer l'opération selon les étapes ci-dessus.


Kutools for Excel - Vous aide à toujours terminer le travail en avance, à avoir plus de temps pour profiter de la vie
Vous vous retrouvez souvent à rattraper le travail, à manquer de temps pour vous et votre famille?  Kutools for Excel peut vous aider à faire face 80 % Excel des puzzles et améliorez l'efficacité du travail de 80 %, vous donnant plus de temps pour prendre soin de votre famille et profiter de la vie.
300 outils avancés pour 1500 scénarios de travail, rendent votre travail tellement plus facile que jamais.
Plus besoin de mémoriser les formules et les codes VBA, reposez désormais votre cerveau.
Les opérations compliquées et répétées peuvent être effectuées une seule fois en quelques secondes.
Réduisez chaque jour des milliers d'opérations clavier et souris, dites adieu aux maladies professionnelles maintenant.
Devenez Excel expert en 3 minutes, vous aide à être rapidement reconnu et à bénéficier d'une promotion d'augmentation de salaire.
110,000 300 personnes hautement efficaces et plus de XNUMX entreprises de renommée mondiale.
Faites de votre 39.0 $ une valeur de plus de 4000.0 $ pour la formation des autres.
Essai gratuit de toutes les fonctionnalités 30-journée. Garantie de remboursement de 60 jours sans raison.

Comments (146)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have one workbook with 100+ sheets, I want to move all 100+ sheets into another workbook in a single sheet.
This comment was minimized by the moderator on the site
I had to read throught the comments to find suggestions that worked for my application of the VBA CODE 2, but I managed to get it to work doing the following things:
1. make sure to change "C:\Users\DT168\Desktop\KTE\" to your own directory to wherever you have your files are located. don't forget the extra "\" at the end!2. my spreadsheets were extension ".xls", so I deleted the extra "x" in this line, like so: xStrFName = Dir(xStrPath & "*.xlsx")3. I placed all my spreadsheets in a single folder, and only those files were the contents of that folder, the target macro enabled spreadsheet where this vba was running from was saved outside of this folder (hopefully this makes sense).
one thing that I didn't want to mess with though is I only needed to merge the 1st tab of each spreadsheet, but I didn't want to mess with the code so if each workbook you want to merge into one has multiple tabs, this code will grab all of the tabs on each workbook and place them in your target spreadsheet, I had to manually delete all the tabs I didn't want.
if the author of this vba could reply to me, how do you change the code to just copy the 1st tab as opposed to all the tabs?
thank you!
This comment was minimized by the moderator on the site
hi I want a change. If the the sheet name is same then the data should be appended in the same name sheet rather than adding a sheet. for example if i have 10 files with jan, feb, mar same sheetnames. then result should be 1 file having jan, feb, mar only 3 sheets with the data of all 10 files. thanks
This comment was minimized by the moderator on the site
Good morning,

Basically I have to copy the values of another file example c: \ test.xlsx (sheet name "date"):

I have to copy the values from A2: T20


And I have to paste in another Extract.xlsx file on the “Extracts” folder on A2.


PLEASE NOTE: You must run vba when opening the file.
This comment was minimized by the moderator on the site
Hello! I need to merge multiple files into one, that are password protected. All source files use the same password. What changes are needed to the first VBA script to merge the files without having to enter the password each time?
This comment was minimized by the moderator on the site
Hello any one can help me, I want to combine sheet 2 only from 5 different sheet, can you script for me.
This comment was minimized by the moderator on the site
you can use Array function to copy the multiple sheets to combine in one file
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
how to use VBA code 1 to amend and make it runs to combine all the .xlsx files into one excel file and each excel spreedsheet tab in the combined excel file should be named as original file name.thanks
This comment was minimized by the moderator on the site
What part of VBA code 3 specifies the name of the worksheet to be copied?
This comment was minimized by the moderator on the site
sir i want to compile different worksheets from different workbook into one master sheet
This comment was minimized by the moderator on the site
Good day,
I recommend the first option "Combine multiple worksheets from workbooks into one worksheet" of the Combine feature in Kutools for Excel to handle this work.
You can open the below hyperlink for more details of this option:
https://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html#a1
This comment was minimized by the moderator on the site
Hi, While combining the worksheets from different workbooks, I want it to be paste special values. How to do that?
This comment was minimized by the moderator on the site
Hello Sir,

When I run the VBA code 1, as per the instructions given. The script ran without any errors. but I could see in the master file

1) each xls file from the directory got created 2 times in the master workbook.

2) if master file name falls in between the other sheet names (ex: file1.xls, file2.xls, file3_master.xls, file4.xls ... ) then file4.xls is not being added into the file3_master.xls file.

file1, file2 got created 2 times...



Please help how to address this ?



Thank you,

Ramesh
This comment was minimized by the moderator on the site
In VBA 2 option my sheet names as Name.xlsx - Can we remove .xlsx?
This comment was minimized by the moderator on the site
hello, vba code 3 isn't worked with me. can anyone help me? i'm using excel 2016. the code hasn't error. but, it can't worked. thanks
This comment was minimized by the moderator on the site
Hello, can anyone advise me please if it is possible to combine workbooks NOT into the one where I have(run) the button with VBA macro BUT to a completely different new workbook? So basically I would like to know if it is possible to create a macro in VBA that would create new workbook(file) with combined data from other workbooks? Would greatly appreciate your help! Thank you!
This comment was minimized by the moderator on the site
Say you want to combine workbooks by fives or twos or tens. So basically, if you have 50 workbooks and you want to combine them by fives, you'll have 10 workbooks, each having 5 workbooks worth of data by the end of it. How do you tweak this data?
This comment was minimized by the moderator on the site
VBA Code2 is working but the sheet names are "Consolidated"1,2 and so on not the original workbook names, How can I get the sheet names as original workbook names. Pls anyone help me..
This comment was minimized by the moderator on the site
Good day,
After applying the above VBA 2, the original worksheets' information (the workbook names) will be added to the corresponding worksheet names as prefix.
This comment was minimized by the moderator on the site
Can we Tweak it to get only orginal sheet name?
This comment was minimized by the moderator on the site
Tanx for this, it helps me a lot... looking forward for more help from you. God bless you always.
This comment was minimized by the moderator on the site
I like using this technique better than using traditional "3D Formula" techniques in Excel.
This comment was minimized by the moderator on the site
I’m glad I could help ^_^
This comment was minimized by the moderator on the site
Note: This VBA code can merge the entire workbooks into the master workbook, if you want to combine specified worksheets of the workbooks, this code will not work.

Can we have the module for VBA that above scene will work,
This comment was minimized by the moderator on the site
Hi Jonel,
The following code can help you solve the problem. You need to replace folder path and "Sheet1, Sheet3" with the specified folder path and worksheets as you need.

Sub MergeSheets2()
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
This comment was minimized by the moderator on the site
When I run this, each sheet in the new workbook is being named based off of the sheet names of the original document rather than the filenames. Any idea what I might be doing wrong?
This comment was minimized by the moderator on the site
Hi Chris,
If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code to solve the problem.

Sub MergeWorkbooks()
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
This comment was minimized by the moderator on the site
Hi Crystal,
how can I copy only the first sheet of each folder?
This comment was minimized by the moderator on the site
It didnt work for me then I realized my files are .xlsx, so added the missing "x" to the Filename line.
This comment was minimized by the moderator on the site
This worked for me but I had to make sure I have to put "\" at the end of my path. Initially, I didn't have it and it wouldn't work.
This comment was minimized by the moderator on the site
The VBA code isn't working for me. I have entered my path but is there anything else that I need to customize to make it run? I can't easily see what else I might need to enter.
This comment was minimized by the moderator on the site
Good day,
Please make sure you have put "\" at the end of your path. Thanks for your comment.
This comment was minimized by the moderator on the site
code not worked can anyone help me
This comment was minimized by the moderator on the site
Big thanks! It was just what I needed
This comment was minimized by the moderator on the site
This doesn't seem to mention the easiest method (at least for a limited number of sheets): simply drag the tab at the bottom of each sheet to the new workbook.
This comment was minimized by the moderator on the site
Hello All, please help me.

I have one old worksheet list with their email address and one new worksheet, how can I merge this two list and take the duplicates and have the one with email address from duplicates
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Thank you for providing the VBA code!
This comment was minimized by the moderator on the site
Hi everyone,

First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.

I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.

In Addition to this I have to merge all the worksheets and have them in another sheet which is called "Übersicht" (Overview). However there is a different column in the sheet and it's between "Nr." and "Thema" columns (which are in A1 and A2 in all the 9 Sheets) and this different column called "Kategorie" (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this "Nr. (A1), Kategorie (A2) and Thema (A3).....".So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in "Übersicht" (Overview) sheet needs to update by itself. How can I do this?

I hope I explained it well. Thanks a lot in advance!

I wish you merry Christmas and a happy new year!

oduff
This comment was minimized by the moderator on the site
Thank you for the VB code it has helped me with my job to make it easy.
This comment was minimized by the moderator on the site
When merging multiple Excel files, how can I get the merge routine to skip the first row on all but the first worksheet (so that only data are in the merged file, not variable names with each new Excel worksheet that gets added to the merged data set)?
This comment was minimized by the moderator on the site
By the way, I'm using Excel 2016 and here's the code I have, which seems to work correctly:

Sub mergeFiles()
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet

sourceWorkbook.Close
Next i

End Sub
This comment was minimized by the moderator on the site
This code is great. One question.

The team I'm building a workbook for gets data from several external sources, and many of the sheets appear similar and have the same name. This makes it hard to identify the sources of data just by looking at the sheets. However, each workbook will have a different file name.

For example, if I pull in three files: Book1, Book2, Book3, and each of them has two sheets: SheetA, SheetB... After all is said and done, there isn't a clear way to distinguish which sheets came from where, since the sheet names will just be: SheetA, SheetB, SheetA (2), SheetB (2), SheetA (3), SheetB (3).

Instead if they could be renamed to SheetABook1, SheetBBook1, SheetABook2, SheetBBook2, etc. they'd be more identifiable. Is there a way to have the VBA tack on the file name to the existing sheet names?
This comment was minimized by the moderator on the site
Update: I have discovered that there is *just* enough variance in column headers such that I can identify which is which, and from there I can rename the sheets based on those properties. It appears that will work for now, but I'd be curious if it's still possible to do what I'd originally thought.
This comment was minimized by the moderator on the site
Run-time error '1004':
Copy method of worksheet class failed
This comment was minimized by the moderator on the site
I am using the code below to combined sheet 1 of multiple workbooks, but now I actually need to combine sheet 2 of multiple work books. Can any one please help me with what I need to change on the coding to combine sheet 2 instead of sheet 1.

Sub MergeFilesWithoutSpaces()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer ThisWB = ActiveWorkbook.Name

path = "c:\Test\"

RowofCopySheet = 2

Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
Set Dest = shtDest.Range("A" & shtDest.Cells(Rows.Count, 1).End(xlUp).Row + 1)
CopyRng.Copy
Dest.PasteSpecial xlPasteFormats
Dest.PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False 'Clear Clipboard'
Wkb.Close False

End If

Filename = Dir()

Loop

End Sub
This comment was minimized by the moderator on the site
Hi, I run into an syntaxis error while I execute this code (I also need just to combine sheet 1 from around 250 seperate .xls files into one file). I am not a VBA specialist.


This below pops up in yellow.
Sub MergeFilesWithoutSpaces()

Another question: do I need to replace the "c:\test\"path by my path where these 250 .xls are stored?
Any other modifications to this code?


Much appreciated!
This comment was minimized by the moderator on the site
i want to combine data from multiple work books (excel file) whc includes 8 sheets
This comment was minimized by the moderator on the site
Error Line: Workbooks.Open Filename:=
This comment was minimized by the moderator on the site
how can I copy specific same cells for expamle (between A1-A15 for each excel sheet )from different files and paste all of them into a worksheet?
This comment was minimized by the moderator on the site
Hi All I have successfully imported couple of excel spread sheets in one sheet by using below mentioned vb script: Sub GetSheets() Path = "Z:\.....\reports\" Filename = Dir(Path & "*.xls") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop However can anyone help me refining above script on how to import both the formats i.e. ".xls" and ".xlsx" of excel spread sheet by using single vb script. Any help would be much appreciated.
This comment was minimized by the moderator on the site
Got the solution for using both the formats i.e. ".xls" and ".xlsx" of excel spread sheet and code is given below: Sub GetSheet() Dim temp As String Path = "Z:\.....\reports\" Filename = Dir(Path & "*.xl??") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True temp = ActiveWorkbook.Name ActiveSheet.Name = ActiveSheet.Name ActiveWorkbook.Sheets(ActiveSheet.Name).Copy After:=ThisWorkbook.Sheets(1) Workbooks(Filename).Close Filename = Dir() Loop End Sub
This comment was minimized by the moderator on the site
I have used below mentioned script and it was successful :-) Sub GetSheets() Dim temp As String Path = "Z:\.....\" Filename = Dir(Path & "*.xlsx") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True temp = ActiveWorkbook.Name ActiveSheet.Name = temp ActiveWorkbook.Sheets(temp).Copy After:=ThisWorkbook.Sheets(1) Workbooks(Filename).Close Filename = Dir() Loop End Sub However facing issue with two different format of excel spread sheets i.e. "xls" and "xlsx" which i would like to import. Any help would be greatly appreciated.
This comment was minimized by the moderator on the site
When I click Finish for Combine Worksheets step 3 of 3, it asks me to save a file name, and then it just sits there.
This comment was minimized by the moderator on the site
THE FOLLOWING CODE WORKED FOR ME IN EXCEL 2016. YOU NEED TO SPECIFY YOUR OWN DIRECTORY IN PLACE OF THE ONE I USED. IN MY CASE THE WERE REQUIRED IN THIS LINE (CONTRARY TO WHAT SOME OTHERS USED ABOVE): Do While Filename "". THE CODE THAT WORKED FOR ME FOLLOWS (I EMPLOYED THE STEPS OUTLINED IN THE ORIGINAL POST TO CREATE THIS CODE): Sub GetSheets() Path = "C:\Users\Kevin\Documents\Combine Excel Workbooks\Workbooks to Combine\" Filename = Dir(Path & "*.xlsx") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=False For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
This comment was minimized by the moderator on the site
Hi,


When execute the above script Workbooks.Open Filename:= shows error expected statment. Could you please helpw me to resolve the issue
This comment was minimized by the moderator on the site
Hi All,


When I execute the above script it shows Line 6 Char 27 Expected Statement. Could you please help me to resolve the issue.
This comment was minimized by the moderator on the site
Thanks to every one,I have tried this program and it was helpful, I had 30 excel files and I wanted to merge them in "bahmann.xlsx". Sub GetSheets() Path = "C:\Users\16262\Desktop\bahman\"--- you have to put "\" at the end of your path Filename = Dir(Path & "*.xlsx") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=False For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=Workbooks("bahmann.xlsx").Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
This comment was minimized by the moderator on the site
Several excel files to merge using access? There are several files on the network.
This comment was minimized by the moderator on the site
Recently we we're solving this task for customer with more than 2000 different information types, hundreds of source excel files. They wanted to merge them into one big sheet to wok with. As a part of this work, we've developer Excel Stats Merger app: https://www.jandrozd.eu/products/excel-stats-merger/ - it does not require MS Office to do the job. You simply define merging rules and then process your files.
This comment was minimized by the moderator on the site
HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.
This comment was minimized by the moderator on the site
THANKS FOR THIS VALUABLE FORMULA....
This comment was minimized by the moderator on the site
HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.[quote]THANKS FOR THIS VALUABLE FORMULA....By kannan[/quote]
This comment was minimized by the moderator on the site
I followed the instructions but when I Run a screen opens that lists Macros. I select GetSheets and Run but nothing happens. Sub GetSheets() Path = "G:\COM\Diabetes Center\Pat\Time Sheets\My time Sheet 2013" Filename = Dir(Path & "*.xlsx") Do While Filename "" Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Loop End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations