Note: The other languages of the website are Google-translated. Back to English
Přihlásit se  \/ 
x
or
x
Registrace  \/ 
x

or

Jak kombinovat více sešitů do jednoho hlavního sešitu v aplikaci Excel?

Už jste někdy uvízli, když musíte kombinovat více sešitů do hlavního sešitu v aplikaci Excel? Nejstrašnější věcí je, že sešity, které musíte kombinovat, obsahují několik pracovních listů. A jak kombinovat pouze zadané listy více sešitů do jednoho sešitu? Tento kurz ukazuje několik užitečných metod, které vám pomohou vyřešit problém krok za krokem.


Kombinujte více sešitů do jednoho sešitu s funkcí Přesunout nebo Kopírovat

Pokud existuje jen pár sešitů, které je třeba kombinovat, můžete pomocí příkazu Přesunout nebo Kopírovat ručně přesunout nebo zkopírovat listy z původního sešitu do hlavního sešitu.

1. Otevřete sešity, které sloučíte do hlavního sešitu.

2. Vyberte listy v původním sešitu, které přesunete nebo zkopírujete do hlavního sešitu.

Poznámky:

1). Můžete vybrat několik nesousedících listů s přidržením Ctrl klíč a klikání na záložky listu jeden po druhém.

2). Chcete-li vybrat více sousedních listů, klikněte na kartu prvního listu a podržte směna klíč a poté kliknutím na kartu poslední list vyberte všechny.

3). Můžete kliknout pravým tlačítkem na libovolnou kartu listu a kliknout na Vyberte všechny listy z místní nabídky vyberte všechny listy v sešitu současně.

3. Po výběru potřebných listů klikněte pravým tlačítkem na kartu listu a poté klikněte na Přesunout nebo Kopírovat z kontextové nabídky. Viz snímek obrazovky:

4. Potom Přesunout nebo Kopírovat objeví se dialogové okno Zarezervovat z rozevíracího seznamu vyberte hlavní sešit, do kterého přesunete nebo zkopírujete listy. Vyberte přesun do konce v Před listem zaškrtněte políčko Vytvořte kopii a nakonec klikněte na OK tlačítko.

Pak můžete vidět listy ve dvou sešitech spojených do jednoho. Chcete-li přesunout listy z jiných sešitů do hlavního sešitu, opakujte výše uvedené kroky.


Zkombinujte více sešitů nebo určených listů sešitů do hlavního sešitu pomocí VBA

Pokud existuje více sešitů, které je třeba sloučit do jednoho, můžete rychle dosáhnout následujících kódů VBA. Postupujte prosím následovně.

1. Vložte všechny sešity, které chcete zkombinovat, do jednoho ve stejném adresáři.

2. Spusťte soubor Excel (tento sešit bude hlavním sešitem).

3. zmáčkni Další + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno. V Microsoft Visual Basic pro aplikace okno, klepněte na tlačítko Vložit > Modul, potom zkopírujte níže uvedený kód VBA do okna modulu.

Kód VBA 1: Sloučení více sešitů aplikace Excel do jednoho

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
	

Poznámky:

1. Výše ​​uvedený kód VBA zachová po sloučení názvy listů původních sešitů.

2. Pokud chcete po sloučení rozlišit, které listy v hlavním sešitu pocházejí, použijte níže uvedený kód VBA 2.

3. Pokud chcete pouze kombinovat určené listy sešitů do hlavního sešitu, může vám pomoci následující kód VBA 3.

V kódech VBA: „C: \ Users \ DT168 \ Desktop \ KTE \„Je cesta ke složce. V kódu VBA 3 „List1, List3„jsou zadané listy sešitů, které zkombinujete do hlavního sešitu. Můžete je změnit podle svých potřeb.

Kód VBA 2: Sloučit sešity do jednoho (každý list bude pojmenován předponou původního názvu souboru):

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

Kód VBA 3: Sloučit určené listy sešitů do hlavního sešitu:

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. zmáčkni F5 klíč ke spuštění kódu. Poté jsou všechny listy nebo určené listy sešitů v určité složce sloučeny do hlavního sešitu najednou.


Snadno kombinujte více sešitů nebo určených listů sešitů do jednoho sešitu

Naštěstí, Kombinovat sešit užitečnost Kutools pro Excel usnadňuje sloučení více sešitů do jednoho. Podívejme se, jak tuto funkci získat kombinací více sešitů.

Před aplikací Kutools pro Excel, Prosím nejprve si jej stáhněte a nainstalujte.

1. Vytvořte nový sešit a klikněte na Kutools Plus > Kombinovat. Poté se zobrazí dialogové okno, které vám připomene, že by měly být uloženy všechny kombinované sešity a funkci nelze použít na chráněné sešity, klikněte na OK tlačítko.

2. V Zkombinujte pracovní listy průvodce, vyberte Zkombinujte více pracovních listů ze sešitů do jednoho sešitu možnost a poté klikněte na další knoflík. Viz screenshot:

3. V Zkombinujte pracovní listy - krok 2 ze 3 dialogové okno, klepněte na tlačítko přidat > Soubor or Desky přidáte soubory Excel, které sloučíte do jednoho. Po přidání souborů aplikace Excel klikněte na úprava tlačítko a vyberte složku pro uložení hlavního sešitu. Viz screenshot:

Nyní jsou všechny sešity sloučeny do jednoho.

Ve srovnání s výše uvedenými dvěma metodami Kutools pro Excel má následující výhody:

  • 1) Všechny sešity a listy jsou uvedeny v dialogovém okně;
  • 2) U listů, které chcete vyloučit ze slučování, jednoduše zrušte jejich zaškrtnutí;
  • 3) Prázdné listy jsou automaticky vyloučeny;
  • 4) Původní název souboru bude po sloučení přidán jako předpona k názvu listu;
  • Pro více funkcí této funkce prosím navštivte zde.

  Pokud chcete mít bezplatnou (30denní) zkušební verzi tohoto nástroje, kliknutím jej stáhněte, a poté přejděte k použití operace podle výše uvedených kroků.


Kutools pro Excel - Pomůže vám vždy dokončit práci před časem, mít více času na radost ze života
Stává se vám často, že hrajete s prací, nedostatkem času pro sebe a rodinu?  Kutools pro Excel vám pomůže vypořádat se s 80% hádankami aplikace Excel a zlepšit 80% efektivitu práce, poskytne vám více času na péči o rodinu a radost ze života.
Díky 300 pokročilým nástrojům pro 1500 XNUMX pracovních scénářů bude vaše práce mnohem jednodušší než kdy dříve.
Už si nemusíte pamatovat vzorce a kódy VBA, dejte svému mozku odpočinout od nynějška.
Složité a opakované operace lze provést jednorázovým zpracováním během několika sekund.
Omezte každý den tisíce operací klávesnicí a myší, rozloučte se s nemocemi z povolání hned teď.
Staňte se odborníkem na Excel za 3 minuty, pomozte rychle získat uznání a povýšení na zvýšení platu.
110,000 300 vysoce efektivních lidí a více než XNUMX světově proslulých společností.
Udělejte ze svých $ 39.0 v hodnotě více než $ 4000.0 školení ostatních.
Bezplatná zkušební verze na 30 dní. 60denní záruka vrácení peněz bez důvodu.

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.
    Shuk · 3 years ago
    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. [b]"xls"[/b] and [b]"xlsx"[/b] which i would like to import. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Ghulam · 4 years ago
    @Ginger41 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.
    Johnny Bravo · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Kevin Coutts · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Ma Hi · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Ma Hi · 4 years ago
    @jlhall07 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.
    E nic · 4 years ago
    Several excel files to merge using access?
    There are several files on the network.
  • To post as a guest, your comment is unpublished.
    E nic · 4 years ago
    @A. Karthi Our office has duplication of data, (i.e.-name, address, city, amount, date signed) from several excel originals and trying to combine the data is going to be a work in progress. How can that be done to eliminate double work and double information entries?
  • To post as a guest, your comment is unpublished.
    Chris F · 4 years ago
    @Susie Go to view and unhide "PERSONAL" - it seems to have trouble executing the whole code while the master is hidden.
    You can make the macro native to that workbook, but you'd have to recreate the whole thing every time you wanted to use it
  • To post as a guest, your comment is unpublished.
    Chris F · 4 years ago
    @Susie [quote name="Susie"]I get a 'runtime error 1004', Copy Method of Worksheet class failed on the line that reads: Sheet.Copy After:=ThisWorkbook.Sheets(1). I'm using Excel 2010.

    Can you assist? Thanks, - Susie[/quote]
    Had the same problem, it works when I go to view and unhide "PERSONAL", it seems to have trouble accessing this macro with the master hidden.
  • To post as a guest, your comment is unpublished.
    Jan Drozd · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Mohideen · 4 years ago
    @Niki Foster 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.
    Mohideen · 4 years ago
    @kannan 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 name="kannan"]THANKS FOR THIS VALUABLE FORMULA....[/quote]
  • 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.
    kannan · 4 years ago
    THANKS FOR THIS VALUABLE FORMULA....
  • To post as a guest, your comment is unpublished.
    pat · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    BWANG · 4 years ago
    Thanks. With some changes, the following works for me:

    Sub GetSheets() 'make sure the module is created in the current Excel spreadsheet "Weather data 201611.xlsx", not the "Personal.xlsb"
    Path = "C:\Weather Data\201611\" 'remember to change the file location here
    Filename = Dir(Path & "*.csv") ' .csv is the type of file while we wanted to open, change to xls or xlsx if required
    Do While Filename ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=False 'Depending on the files which we want to open, if it is a read only file then change to ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=Workbooks("Weather data 201611.xlsx").Sheets(1) 'Remember to change the file name to the file name while is new open for this module. Make sure the type match as well
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub
  • To post as a guest, your comment is unpublished.
    Rakesh G.B.Groups · 4 years ago
    i want to mix or update a excel file by a old file data and new file data
    please suggest me command

    Thank you
  • To post as a guest, your comment is unpublished.
    wackiemark · 4 years ago
    hello can anyone please help me with this problems in different codes.

    1.) copy all sheets in selected workbooks in a folder and paste it to current workbook. and if the worksheets have same sheetname, it will add a number e.g (1) on the side of the sheetname.

    2.) copy specific worksheet in selected workbooks and paste to current workbook, and if the worksheets have same sheetname, it will add a number e.g (1) on the side of the sheetname.

    thank you in advance
  • To post as a guest, your comment is unpublished.
    Ashwath · 4 years ago
    Hello, I tried this code for combining the files and it worked well. However I have a situation where in I have to combine the data from different sheets of multiple files in to one master file with multiple sheets.

    For ex: I have 10 different file and each file has data in Sheet 1 and Sheet 2. I want to create a consolidate data in to one master and all data from sheet 1 of multiple files should be consolidated in one sheet 1 of master and sheet 2 in sheet of master. Can this be done?
  • To post as a guest, your comment is unpublished.
    cindy · 4 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 · 4 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 · 4 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 4 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.
    Taslima · 4 years ago
    @A. Karthi KUTOOLS Awsome solution.

    I need one more help when I create master workbook then cell color of work sheet is changed from original worksheet.

    How can I keep it like original worksheet.
  • To post as a guest, your comment is unpublished.
    wooly · 4 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.
    samuel Birch · 5 years ago
    @Susie Hey Susie,

    Ive been working on this problem for a while now myself, getting the same error.

    Check to see if the module was created under PERSONAL instead of your active workbook.

    Once I created the module under the correct tree, the below code worked just fine.

    Sub GetSheets_xls()
    Dim Sheet As Worksheet
    Path = "C:\Users\yournamehere\Desktop\Testingfolder\"
    Filename = Dir(Path & "*.xls")
    Do While Filename ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=False
    Set Sheet = ActiveWorkbook.Sheets(1)
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
    'Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop
    End Sub




    Hope this helps!
  • 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.
    Zadius · 5 years ago
    @RichardHead [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.
    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.
    Adriano Marcato · 5 years ago
    @Code 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.
    DaisyD · 5 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 · 5 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 · 5 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 · 5 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 · 5 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 · 5 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.
    fux · 5 years ago
    @Greg 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.
    Snabo · 5 years ago
    Excellent VBA code. Thanks
  • To post as a guest, your comment is unpublished.
    Sachin · 5 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 · 5 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 · 5 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 · 6 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 · 6 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