Note: The other languages of the website are Google-translated. Back to English
Accedi  \/ 
x
or
x
Registrati  \/ 
x

or

Come combinare più cartelle di lavoro in una cartella di lavoro principale in Excel?

Sei mai stato bloccato quando devi combinare più cartelle di lavoro in una cartella di lavoro principale in Excel? La cosa più terribile è che le cartelle di lavoro che devi combinare contengono più fogli di lavoro. E come combinare solo i fogli di lavoro specificati di più cartelle di lavoro in una cartella di lavoro? Questo tutorial mostra diversi metodi utili per aiutarti a risolvere il problema passo dopo passo.


Combina più cartelle di lavoro in una cartella di lavoro con la funzione Sposta o Copia

Se è necessario combinare solo un paio di cartelle di lavoro, è possibile utilizzare il comando Sposta o Copia per spostare o copiare manualmente i fogli di lavoro dalla cartella di lavoro originale alla cartella di lavoro principale.

1. Apri le cartelle di lavoro che unirai in una cartella di lavoro principale.

2. Selezionare i fogli di lavoro nella cartella di lavoro originale che sposterete o copierete nella cartella di lavoro principale.

Note:

1). Puoi selezionare più fogli di lavoro non adiacenti tenendo premuto il tasto Ctrl e facendo clic sulle schede del foglio una per una.

2). Per selezionare più fogli di lavoro adiacenti, fare clic sulla prima scheda del foglio, tenere premuto il pulsante spostamento chiave, quindi fare clic sulla scheda dell'ultimo foglio per selezionarli tutti.

3). Puoi fare clic con il tasto destro su qualsiasi scheda del foglio, fare clic su Seleziona tutti i fogli dal menu contestuale per selezionare tutti i fogli di lavoro nella cartella di lavoro contemporaneamente.

3. Dopo aver selezionato i fogli di lavoro necessari, fare clic con il pulsante destro del mouse sulla scheda del foglio, quindi fare clic su Sposta o copia dal menu contestuale. Vedi screenshot:

4. Quindi il file Sposta o copia si apre la finestra di dialogo, nel file Prenotare menu a discesa, selezionare la cartella di lavoro principale in cui spostare o copiare i fogli di lavoro. Seleziona mossa per terminare nel file Prima del foglio casella, seleziona il file Crea una copia casella e infine fare clic su OK pulsante.

Quindi puoi vedere i fogli di lavoro in due cartelle di lavoro combinate in una. Ripetere i passaggi precedenti per spostare i fogli di lavoro da altre cartelle di lavoro nella cartella di lavoro principale.


Combina più cartelle di lavoro o fogli di lavoro specificati in una cartella di lavoro principale con VBA

Se è necessario unire più cartelle di lavoro in una sola, è possibile applicare i seguenti codici VBA per ottenerlo rapidamente. Si prega di fare quanto segue.

1. Metti tutte le cartelle di lavoro che desideri combinare in una nella stessa directory.

2. Avvia un file Excel (questa cartella di lavoro sarà la cartella di lavoro principale).

3. premi il altro + F11 chiavi per aprire il Microsoft Visual Basic per le applicazioni finestra. Nel Microsoft Visual Basic per le applicazioni finestra, fare clic inserire > Modulo, quindi copia sotto il codice VBA nella finestra del modulo.

Codice VBA 1: unisci più cartelle di lavoro Excel in una sola

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
	

Note:

1. Il codice VBA sopra manterrà i nomi dei fogli delle cartelle di lavoro originali dopo la fusione.

2. Se si desidera distinguere quali fogli di lavoro nella cartella di lavoro principale provengono da dove dopo l'unione, applicare il codice VBA di seguito 2.

3. Se si desidera semplicemente combinare fogli di lavoro specificati delle cartelle di lavoro in una cartella di lavoro principale, il codice VBA 3 di seguito può essere d'aiuto.

Nei codici VBA, "C: \ Users \ DT168 \ Desktop \ KTE \"È il percorso della cartella. Nel codice VBA 3, "Sheet1, Sheet3"sono i fogli di lavoro specificati delle cartelle di lavoro che combinerai in una cartella di lavoro principale. Puoi modificarli in base alle tue esigenze.

Codice VBA 2: unisci le cartelle di lavoro in una (ogni foglio di lavoro verrà denominato con il prefisso del nome file originale):

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

Codice VBA 3: unisci i fogli di lavoro specificati delle cartelle di lavoro in una cartella di lavoro principale:

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. premi il F5 chiave per eseguire il codice. Quindi tutti i fogli di lavoro o fogli di lavoro specificati delle cartelle di lavoro in una determinata cartella vengono combinati contemporaneamente in una cartella di lavoro principale.


Combina facilmente più cartelle di lavoro o fogli di cartelle di lavoro specificati in una cartella di lavoro

Fortunatamente la documentazione dedicata a Combinare utilità cartella di lavoro di Kutools for Excel rende molto più semplice unire più cartelle di lavoro in una sola. Vediamo come far funzionare questa funzione combinando più cartelle di lavoro.

Prima di applicare Kutools for Excel, Per favore scaricalo e installalo prima.

1. Creare una nuova cartella di lavoro e fare clic su Kutools Plus > Combinare. Quindi viene visualizzata una finestra di dialogo per ricordarti che tutte le cartelle di lavoro combinate devono essere salvate e la funzione non può essere applicata alle cartelle di lavoro protette, fare clic su OK pulsante.

2. Nel Combina fogli di lavoro procedura guidata, selezionare Combina più fogli di lavoro da cartelle di lavoro in una cartella di lavoro opzione, quindi fare clic su Prossimo pulsante. Vedi screenshot:

3. Nel Combina fogli di lavoro - Passaggio 2 di 3 finestra di dialogo, fare clic su Aggiungi > Compila il or cartella per aggiungere i file Excel che unirai in uno solo. Dopo aver aggiunto i file Excel, fare clic su Fine e scegli una cartella in cui salvare la cartella di lavoro principale. Vedi screenshot:

Ora tutte le cartelle di lavoro vengono unite in una sola.

Rispetto ai due metodi precedenti, Kutools for Excel ha i seguenti vantaggi:

  • 1) Tutte le cartelle di lavoro e i fogli di lavoro sono elencati nella finestra di dialogo;
  • 2) Per i fogli di lavoro che desideri escludere dall'unione, deselezionala semplicemente;
  • 3) I fogli di lavoro vuoti vengono esclusi automaticamente;
  • 4) Il nome del file originale verrà aggiunto come prefisso al nome del foglio dopo l'unione;
  • Per ulteriori funzioni di questa funzione, si prega di visitare qui.

  Se desideri avere una prova gratuita (30 giorni) di questa utility, fare clic per scaricarlo, quindi andare ad applicare l'operazione secondo i passaggi precedenti.


Kutools per Excel - Ti aiuta a finire il lavoro sempre in anticipo, ad avere più tempo per goderti la vita
Ti capita spesso di ritrovarti a giocare al passo con il lavoro, la mancanza di tempo da dedicare a te stesso e alla famiglia?  Kutools for Excel può aiutarti a gestire l'80% dei puzzle Excel e migliorare l'80% dell'efficienza lavorativa, darti più tempo per prenderti cura della famiglia e goderti la vita.
300 strumenti avanzati per 1500 scenari di lavoro, rendono il tuo lavoro molto più semplice che mai.
Non è più necessario memorizzare formule e codici VBA, da ora in poi dai una pausa al tuo cervello.
Le operazioni complicate e ripetute possono essere eseguite una sola volta in pochi secondi.
Riduci migliaia di operazioni con tastiera e mouse ogni giorno, saluta subito le malattie professionali.
Diventa un esperto di Excel in 3 minuti, ti aiuta a ottenere rapidamente un riconoscimento e una promozione di aumento di stipendio.
110,000 persone altamente efficienti e la scelta di oltre 300 aziende di fama mondiale.
Rendi i tuoi $ 39.0 più di $ 4000.0 di formazione di altri.
30 giorni di prova gratuita con funzionalità complete. Garanzia di rimborso di 60 giorni senza motivo.

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    cindy · 5 years ago
    thanks for the macro guided for 'Combine multiple workbooks to one workbook with VBA'.

    However, i do have an additional cirtumtances.
    Let's take your example.
    In the folder 'dt kte', there are 4 workbook (book1, book2, book3 & book4)
    The macro will merge all worksheet in every single workbook into my excel file.
    However, the circumtances i need is:-
    in every workbook, there is mutual sheet named "HIJ".

    what would be the macro if i only want to merge the HIJ worksheet in book1 to book4, into one workbook i want?
  • To post as a guest, your comment is unpublished.
    MUHAMMAD MURTAZA · 5 years ago
    ASSALAM O ALIKUM,,

    LOT OF THANKS FOR COMBINE WORK SHEET IN MASTER SHEET
  • To post as a guest, your comment is unpublished.
    Gnanesh · 5 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 5 years ago
    The above code works well, but it seems that it doesn't follow any certain order, in my folder, each excel file ( before merging ) has 1 sheet with same name,for exp: book1 contains sheet named A, book2 also contains sheet named A ,....then after merging by VBA, the combined workbook contains sheets named A, A(1), A(2), A(3),...in random order, A(1) actually doesn't belongs to initial book1. So it's hard for me to identify, I can't know each sheet originally belongs to which file. The result I expect is that all sheets will be combine in name order of original separated file

    Perhaps i can't explain my point well, if someone has same situation or can understand me, then pls kindly upgrade vba code that can combine sheets in order of alphabet name or ascending number ?
  • To post as a guest, your comment is unpublished.
    wooly · 5 years ago
    I'm new to Excel and am struggling to identify what I need to personalise in this code to make it work. Obviously "path" but is that it? as I'm getting errors on the two "set copyrng" and "set dest" row.

    None of these worked for me

    I finally got this one to work. FYI I am using 2010

    'Description: Combines all files in a folder to a master file.
    Sub MergeFiles()
    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

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook. Name

    path = "mypath....." ' Dont't forget to change this

    Application.Ena bleEvents = False
    Application.Scr eenUpdating = 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).R ange(Cells(Rowo fCopySheet, 1), Cells(ActiveShe et.UsedRange.Ro ws.Count, ActiveSheet.Use dRange.Columns. Count))
    Set Dest = shtDest.Range(" A" & shtDest.UsedRan ge.SpecialCells (xlCellTypeLast Cell).Row + 1)
    CopyRng.Copy Dest
    Wkb.Close False
    End If

    Filename = Dir()
    Loop

    Range("A1").Sel ect

    Application.Ena bleEvents = True
    Application.Scr eenUpdating = True

    MsgBox "Done!"
    End Sub
  • To post as a guest, your comment is unpublished.
    Gaurav Sethi · 5 years ago
    Hi,

    My name is Gaurav, I have 10 Excel workbook & i want to add all file in one workbook. Please suggest.
  • To post as a guest, your comment is unpublished.
    Shubham · 5 years ago
    GREAT STUFF!! You saved me a lot of effort!
  • To post as a guest, your comment is unpublished.
    Summer · 5 years ago
    I love playing around with VBA. It's like magic - Poof! and everything is done for you. I've been in programming for over 10 years but I still enjoy the sight :D
  • To post as a guest, your comment is unpublished.
    RichardHead · 5 years ago
    Make sure you add a \ at the end of your path.

    Example:

    Bad: Path = "C:\Users\dt\Desktop\dt kte"

    Good: Path = "C:\Users\dt\Desktop\dt kte\"
    • To post as a guest, your comment is unpublished.
      Zadius · 5 years ago
      [quote name="RichardHead"]Make sure you add a \ at the end of your path.

      Example:

      Bad: Path = "C:\Users\dt\Desktop\dt kte"

      Good: Path = "C:\Users\dt\Desktop\dt kte\"[/quote]

      This worked for the people saying nothing happens!!! Thanks soooo much!
  • To post as a guest, your comment is unpublished.
    DaisyD · 6 years ago
    Thanks for this. Only your website gave me the solution for what I was looking for. Very helpful and correct.
  • To post as a guest, your comment is unpublished.
    Samuel · 6 years ago
    I am having data for all the 365 days for 14 year in one excel sheet.

    Now we want to separate the data year wise (Ex. one excel file for year 2002 in that 12 Sheet ie for 12 month and another excel file for year 2003 in that 12 Sheet ie for 12 month).

    We are able to separate month wise data (by using Excel Kutools - Split Data) and it will create excel sheet for each month (ie it will create totally 24 sheets) and then we should Split to Workbook and it will be 24 excel file and again we should combine 12 file for each year. This is taking lot of time.

    Now our problem is while splitting the date we want Excel to create Year wise Excel files and in each excel file 12 sheets for 12 months. Can we do this in same time.

    Please tell us because we are having lots of data and it is taking lot of time.
  • To post as a guest, your comment is unpublished.
    krishna sharma · 6 years ago
    i have 3 excel work book and each work book has 31 sheet. i want to make one work book with 31 sheet . it is necessary that all sheet of work book must be add.
  • To post as a guest, your comment is unpublished.
    Sourabh · 6 years ago
    Hi !!
    I need help...
    I want to split the data from one excel to different excels.
    having the data of national level and split the same in region wise in seperate excel along with the all sheet that i have in my master data.
    Please help...
  • To post as a guest, your comment is unpublished.
    Greg · 6 years ago
    Hello,
    I see many people interested thoughts..

    Maybe someone could help me with similar problem, please?!

    I need to take 1 specific sheet from a files stored within 1 folder, (all those files have got the same sheet name) - and place that sheets into the new workbook. Anyone has done something like this? I know that I need to use Copy or Move - and the Loop within the folder - its similar like this but I can't find solution...
  • To post as a guest, your comment is unpublished.
    Chris · 6 years ago
    I can't get the following to work... Any help is much appreciated.

    Sub Merge2MultiSheets()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "\\MyPath\etc\etc..."
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(C \ Users \ Chris \ Desktop \ Planet Soccer \ Reports And Buying \ UPCS)

    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""

    Set wbSrc = Workbooks.Open(Filename:=CUsers \ Chris \ Desktop \ PlanetSoccer \ Reports And Buying \ UPCS)

    Set wsSrc = wbSrc.Worksheets(1)

    wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

    wbSrc.Close False

    strFilename = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.Dis playAlerts = True
    Application.Ena bleEvents = True
    Application.Scr eenUpdating = True

    End Sub
  • To post as a guest, your comment is unpublished.
    Snabo · 6 years ago
    Excellent VBA code. Thanks
  • To post as a guest, your comment is unpublished.
    Sachin · 6 years ago
    I need to consolidate data from Sheet1 of multiple workbooks into Sheet1 of a separate workbook. Please help. (All the workbooks contain similar set of data with headers)
  • To post as a guest, your comment is unpublished.
    imran · 6 years ago
    Is this possible to link multiple sheet in one master sheet .eg if we need to extract data in one single sheet without going in to multiple sheet !
  • To post as a guest, your comment is unpublished.
    vijay · 6 years ago
    thanks for script, i would like to like to get file name adjacent to data ie source file for each data. can someone modify script suitably.
  • To post as a guest, your comment is unpublished.
    arun kumar · 6 years ago
    hi i am using ur coding its show error
  • To post as a guest, your comment is unpublished.
    Joanne K · 6 years ago
    Trying to use Kutools to combine multiple workbooks that contain graphs, and for some reason the sheets with graphs do not get included. Please advise.
  • To post as a guest, your comment is unpublished.
    nEELAM SHARMA · 6 years ago
    pls thanks for your guidelines
  • To post as a guest, your comment is unpublished.
    Karan kumar · 6 years ago
    Woooowww... this is a great software for me !!!

    My all work is done
    thanks a lot for this
  • To post as a guest, your comment is unpublished.
    swattie · 7 years ago
    My code works great but only if it is pulling from files saved to the computer. Will it not work if the files I am merging are on a shared drive? I appreciate the help!
  • To post as a guest, your comment is unpublished.
    Purnendu Biswas · 7 years ago
    I have a work book, which have 256 sheets.
    how to collate the sheets.
    File name- "My Codes"
    Sheets name- Table 1 to Table 256
  • To post as a guest, your comment is unpublished.
    Shady39 · 7 years ago
    Thanks Ginger41!!!!

    Works perfectly. I'm using Excel 2010.

    Only issue I had was when I copied Ginger41's sub, it for some reason put gaps in weird places. Just read through and tidy up any gaps (or run the Debug and it will pick them up).
  • To post as a guest, your comment is unpublished.
    Trease · 7 years ago
    I need to combine six spreadsheets into one. Some workbooks have multiple tabs and even though some of the info is the same, all is not. I need to turn it all into one Spreadsheet and all like categories added together for a final sum. Can this help me?
  • To post as a guest, your comment is unpublished.
    Aidan · 7 years ago
    Hi,
    I want to setup my a master set of excel documents in a folder and give them all the files names which will be numbers only (eg 0001, 0002, 0003 and so on. Information contained within them will be feeding a master document also within the same folder. I have to set up all the links at the start so that i can copy the entire folder and reuse on the next project. So its basically a simply link, problem is I want to allow the feeder documents name to change slightly throughout the project. So feeder document 0001 may become '0001 Job12', 0002 might become '0002 roadworks' and so on. I can manually update the links but there will be 100 feeder excel documents, each containing loads of links. Can I maintain the link, even if i change the document name slightly ?
  • To post as a guest, your comment is unpublished.
    John · 7 years ago
    Excel 2010. Script as is did work, but I do not have the 'webbrowser-like tabs" at the top of the newly combined workbook as illustrated at the top of the page. Is the illustration just a well placed ad???
  • To post as a guest, your comment is unpublished.
    santhosh.s · 7 years ago
    :lol: VERY GOOD SHORT CUT IN EXCEL MY DEAR PEPOLE
  • To post as a guest, your comment is unpublished.
    Dr. Paul B. · 7 years ago
    That code was great! Thanks so much.
  • To post as a guest, your comment is unpublished.
    Aren · 7 years ago
    I use an app rather than code and recode. Data Amalgamation by pasync
  • To post as a guest, your comment is unpublished.
    khar booza · 7 years ago
    This is very hard, is it possible you can do it for me?
  • To post as a guest, your comment is unpublished.
    Niki Foster · 7 years ago
    None of these worked for me

    I finally got this one to work. FYI I am using 2010

    'Description: Combines all files in a folder to a master file.
    Sub MergeFiles()
    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

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook.Name

    path = "mypath....." ' Dont't forget to change this

    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(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
    Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
    CopyRng.Copy Dest
    Wkb.Close False
    End If

    Filename = Dir()
    Loop

    Range("A1").Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox "Done!"
    End Sub
    • To post as a guest, your comment is unpublished.
      Mohideen · 4 years ago
      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.
    • To post as a guest, your comment is unpublished.
      michelle · 7 years ago
      How do I edit this so that the data pulled in always starts on the top row? If I run this code twice, it adds the data to the end of my previous data (from the first run of the macro).
      • To post as a guest, your comment is unpublished.
        orivera · 7 years ago
        Change this line:

        RowofCopySheet = 2

        to

        RowofCopySheet = 1
  • To post as a guest, your comment is unpublished.
    Ginger41 · 7 years ago
    Try this out...I got this from another site but unfortunately I can't remember the lady's name so my apologies for not giving her a mention, my bad"

    Combine multiple WB's in Excel:
    REMEMBER TO CHANGE MyPath = !

    Sub Merge2MultiSheets()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "\\MyPath\etc\etc..."
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xls", vbNormal)

    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""

    Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

    Set wsSrc = wbSrc.Worksheets(1)

    wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

    wbSrc.Close False

    strFilename = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub
    • To post as a guest, your comment is unpublished.
      Ghulam · 4 years ago
      Hi
      I added the code into a module. Named the Excel book Masterfile.

      Where in the code do I add. thank you
  • To post as a guest, your comment is unpublished.
    Dileep · 7 years ago
    I Have a workbook it contain around 250 Sheet . I need to Cobain in one sheet. please give me a solution
  • To post as a guest, your comment is unpublished.
    christian · 7 years ago
    i have 112 excel sheets i want to put into a single sheet without copy and paste. Please help me out.
  • To post as a guest, your comment is unpublished.
    Nazeer · 7 years ago
    Hi,

    I tried using the above macros to collate few files, unfortunately no results...

    can some one help me getting rid of manually collating files.
  • To post as a guest, your comment is unpublished.
    Anand Darbha · 7 years ago
    I want to combine all the sheets into one sheet where the headings are common...pls help
  • To post as a guest, your comment is unpublished.
    Dave · 7 years ago
    Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

    Sub GetSheets()
    Path = "c:\temp\"
    Filename = Dir(Path & "*.xls")
    Do While Filename ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub
    • To post as a guest, your comment is unpublished.
      Patrick · 7 years ago
      I was looking for something along these lines but wanted to comment. Doesn't the Do While Filename "" need to be something other than ""? or am I reading that wrong?

      Maybe Do While NOT filename = ""

      Just a thought...
    • To post as a guest, your comment is unpublished.
      jlhall07 · 7 years ago
      [quote name="Dave"]Try this for newer versions of excel. I saved my workbook as catalog, all the files are in c:\temp.

      Sub GetSheets()
      Path = "c:\temp\"
      Filename = Dir(Path & "*.xls")
      Do While Filename ""
      Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
      For Each Sheet In ActiveWorkbook.Sheets
      Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
      Next Sheet
      Workbooks(Filename).Close
      Filename = Dir()
      Loop
      End Sub[/quote]

      I keep getting "can't assign to read-only property" concerning the path... Any idea?
      • To post as a guest, your comment is unpublished.
        Ma Hi · 4 years ago
        Maybe you should change
        ReadOnly:=True
        to
        ReadOnly:=False,
        I have done and it was helpful
      • To post as a guest, your comment is unpublished.
        sara · 7 years ago
        Im having tis problem too. Did you figure it out?
        • To post as a guest, your comment is unpublished.
          jlhall07 · 7 years ago
          Nothing yet... Haven't found any solution or had anyone suggest a fix. Sorry...
          • To post as a guest, your comment is unpublished.
            Greg · 7 years ago
            Me too. This was working 6 months ago, the last time I had to run it. Has anyone found the solution yet? If you had it running before and now it won't work could it be something to do with an update by Microsoft? This is really handy tool for my tasks and saves me loads of time. What could have changed that would cause Excel to start displaying this message all of a sudden? Being fairly new to VBA I have little idea where to begin analysing the logic.

            Kind regards,

            Greg. Glasgow, Scotland.
            • To post as a guest, your comment is unpublished.
              fux · 6 years ago
              It seems that "Path" is now reserved so use just any other name and replace "Path", e.g. "Mypath".
  • To post as a guest, your comment is unpublished.
    Dave · 7 years ago
    for newer versions of excel, try this. I saved my open workbook as catalog, and all the files are in c:\temp.

    Sub GetSheets()
    Path = "c:\temp\"
    Filename = Dir(Path & "*.xls")
    Do While Filename ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=Workbooks("catalog.xlsx").Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub
  • To post as a guest, your comment is unpublished.
    Sherrill · 7 years ago
    Dear Sir/Madam: I followed the steps of "Combine multiple workbooks to one workbook with VBA" to set up the following module, but nothing happened. Would you help me find the problem? thank you



    Sub GetSheets()
    Path = "p:\download\macro\"
    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
    End Sub
  • To post as a guest, your comment is unpublished.
    Sherrill · 7 years ago
    I followed the steps in "Combine multiple workbooks to one workbook with VBA" and click on "run", nothing happend. I am not aware of errors and not sure how to correct. Would you help me? the following is the code I input in a new workbook.

    Thank you

    [b]Sub GetSheets()
    Path = "p:\download\macro\"
    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
    End Sub[/b]
  • To post as a guest, your comment is unpublished.
    Dado · 7 years ago
    After combining the worksheets in one Workbook ,how do you save it I couldn't save it is named Book1 and I am clicking on save or save as but not working.any suggestions?
  • To post as a guest, your comment is unpublished.
    Ness · 7 years ago
    How do you get it to update the changes from the original workbook? I'm trying to get a national summary which will have each region input their data into their own workbooks and then have the national summary which updates from this? I'd like to have this set up for the full year at the beginning and not be working retrospectively.
  • To post as a guest, your comment is unpublished.
    hamid · 8 years ago
    thanks for sharing your knowledge
  • To post as a guest, your comment is unpublished.
    Dinesh · 8 years ago
    Thanks much for the valuable information. This really works. The steps listed in this article really made my job easier.

    Thanks,
    Dinesh
  • To post as a guest, your comment is unpublished.
    Amol · 8 years ago
    Thanks mate, u made my day from this very helpful website... Actually i was also wanted to combine the same header data of different sheets into 1 master worksheet, the KUTOOL for Excel helped me a lot.... Thank you once again.... :)
  • To post as a guest, your comment is unpublished.
    Tedi · 8 years ago
    This is superb :lol: helped me a lot....
  • To post as a guest, your comment is unpublished.
    Henrik · 8 years ago
    Hey

    Great tip. Did [i]almost[/i] all I wanted.

    In the combinde woorkbook, I would have lovede for the worksheet name to contain the name of the original woorkbook, so I know which woorkbook the data comes from.

    The data I'm combining, is from different archives. I have to search for an entry, but don't know which archive it's in.

    So by combining all the data in one file will make it possible for me to search all archives at once.

    But I still need to know, which archive the entry is in.

    Henrik
    • To post as a guest, your comment is unpublished.
      Code · 7 years ago
      For the code to incorporate the file name just do this.

      Sub GetSheets()
      Dim temp As String
      Path = "C:\Users\....\Desktop\Excel combine\"
      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

      Note: this is to only copy the first sheet, it can be tweeked to do all sheets
      • To post as a guest, your comment is unpublished.
        Adriano Marcato · 5 years ago
        It's a great solution indeed. thank you.
        One Problem though, when I execute it like this excel will ask if I want to save alterations before closing (Since the name was changed), and I don't want to do it for every file (around 32 per execution).
        Would there be a way to solve this?
      • To post as a guest, your comment is unpublished.
        LAW · 7 years ago
        How do you incorporate more sheets and how do you specify a different master file to paste all the sheets.