팁 : 다른 언어는 Google 번역입니다. 방문하실 수 있습니다. English 이 링크의 버전.
로그인
x
or
x
x
회원가입
x

or

여러 통합 문서를 Excel에서 하나의 마스터 통합 문서로 결합하는 방법?

Excel에서 여러 통합 문서를 마스터 통합 문서로 결합해야 할 때 어려움을 겪었습니까? 가장 어려운 점은 결합해야하는 통합 문서에 여러 워크 시트가 포함되어 있다는 것입니다. 그리고 여러 통합 문서의 지정된 워크 시트 만 하나의 통합 문서로 결합하는 방법은 무엇입니까? 이 자습서에서는 문제 단계를 단계별로 해결하는 데 도움이되는 몇 가지 유용한 방법을 보여줍니다.


몇 번의 클릭만으로 여러 통합 문서를 하나로 통합하기

통합 문서 결합 ~의 유용성 Excel 용 Kutools 특정 통합 문서를 하나로 통합 할 수 있습니다. 몇 번의 클릭만으로 90 %의 시간을 절약 할 수 있습니다. 또한이 기능을 사용하면 통합 문서의 여러 워크 시트 또는 동일한 이름 워크 시트를 하나로 통합하거나 필요에 따라 통합 문서의 값을 하나의 시트로 통합 할 수 있습니다. Excel 용 Kutools의 60-day 무료 트레일 전체 기능을 지금 다운로드하십시오!

이동 또는 복사 기능을 사용하여 여러 통합 문서를 하나의 통합 문서로 결합

몇 개의 통합 문서를 결합해야하는 경우 이동 또는 복사 명령을 사용하여 수동으로 원본 통합 문서의 워크 시트를 마스터 통합 문서로 이동하거나 복사 할 수 있습니다.

1. 마스터 통합 문서로 병합 할 통합 문서를 엽니 다.

2. 마스터 통합 문서로 이동하거나 복사 할 원본 통합 문서의 워크 시트를 선택하십시오.

노트:

1). 여러 인접하지 않은 워크 시트를 선택하여 Ctrl 키 키를 누르고 시트 탭을 하나씩 클릭하십시오.

2). 인접한 워크 시트를 여러 개 선택하려면 첫 번째 시트 탭을 클릭하고 변화 키를 누른 다음 마지막 시트 탭을 눌러 모두 선택하십시오.

3). 시트 탭을 마우스 오른쪽 버튼으로 클릭하고 모든 시트 선택 컨텍스트 메뉴에서 통합 문서의 모든 워크 시트를 동시에 선택합니다.

3. 필요한 워크 시트를 선택한 후 시트 탭을 마우스 오른쪽 단추로 클릭 한 다음 이동 또는 복사 컨텍스트 메뉴에서. 스크린 샷보기 :

4. 그런 다음 이동 또는 복사 대화 상자가 열립니다. 예약하기 드롭 다운 목록에서 워크 시트를 이동하거나 복사 할 마스터 통합 문서를 선택하십시오. 이동을 선택하여 시트 전 상자에 복사본 만들기 상자를 클릭하고 OK 버튼을 클릭합니다.

그런 다음 두 통합 문서의 워크 시트가 하나로 통합 된 것을 볼 수 있습니다. 위의 단계를 반복하여 다른 통합 문서의 워크 시트를 마스터 통합 문서로 이동하십시오.

Office에서 탭 편집 및 찾아보기
--- 작업이 훨씬 쉬워집니다
사무실 탭 여러 문서를 탐색하고 편집 할 때 작업 시간의 50 %를 절약 할 수 있습니다.
믿을 수 없는! 두 개 이상의 문서 작업은 단일 문서 작업보다 즐겁습니다.
매일 수천 건의 키보드 및 마우스 작동을 줄이고 지금 직업병에 이별하십시오.
Office Tab의 인터페이스는 인터넷 브라우저보다 훨씬 강력하고 효율적입니다.

여러 통합 문서 또는 지정된 통합 문서를 VBA를 사용하여 마스터 통합 문서에 결합

여러 통합 문서를 하나로 병합해야하는 경우 다음 VBA 코드를 적용하여 신속하게 달성 할 수 있습니다. 다음과 같이하십시오.

1. 결합 할 모든 통합 문서를 동일한 디렉토리에 하나 넣습니다.

2. Excel 파일을 실행하십시오 (이 통합 문서는 마스터 통합 문서 일 것입니다).

3. 누르세요 다른 + F11 키를 눌러 응용 프로그램 용 Microsoft Visual Basic 창문. 에서 응용 프로그램 용 Microsoft Visual Basic 창을 클릭합니다 끼워 넣다 > 모듈그런 다음 VBA 코드 아래의 모듈 창으로 복사하십시오.

VBA 코드 1 : 여러 Excel 통합 문서를 하나로 병합

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
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
	

노트:

1. 위의 VBA 코드는 병합 후 원본 통합 문서의 시트 이름을 유지합니다.

2. 병합 후 어디에서 왔는지 마스터 통합 문서의 워크 시트를 구별하려면 아래 VBA 코드 2을 적용하십시오.

3. 통합 문서의 지정된 워크 시트를 마스터 통합 문서에 결합하려는 경우 아래 VBA 코드 인 3을 사용하면 도움이됩니다.

VBA 코드에서 "C : \ Users \ DT168 \ Desktop \ KTE \"는 폴더 경로입니다. VBA 코드 3에서 "Sheet1, Sheet3"는 마스터 통합 문서에 결합 할 통합 문서의 지정된 워크 시트이며 필요에 따라 변경할 수 있습니다.

VBA 코드 2 : 통합 문서를 하나에 병합합니다 (각 워크 시트의 이름은 원래 파일 이름의 접두어로 지정됩니다).

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

VBA 코드 3 : 통합 문서의 지정된 워크 시트를 마스터 통합 문서로 병합 :

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. 누르세요 F5 키를 눌러 코드를 실행하십시오. 그런 다음 모든 워크 시트 또는 특정 폴더에있는 통합 문서의 지정된 워크 시트가 한 번에 마스터 통합 문서로 결합됩니다.

여러 통합 문서 또는 지정된 통합 문서를 하나의 통합 문서로 쉽게 결합

다행히도 결합 통합 문서 유틸리티 Excel 용 Kutools 여러 통합 문서를 하나로 쉽게 병합 할 수 있습니다. 이 기능을 여러 통합 문서를 결합하는 방법을 살펴 보겠습니다.

1. 새 통합 문서를 만들고을 클릭하십시오. Kutools 기타 > 결합. 그런 다음 통합 된 모든 통합 문서를 저장해야하며 보호 된 통합 문서에 기능을 적용 할 수 없다는 대화 상자가 나타납니다. OK 버튼을 클릭합니다.

2. 에서 워크 시트 결합 마법사, 선택 통합 문서의 여러 워크 시트를 하나의 통합 문서로 결합 옵션을 클릭 한 다음 다음 것 단추. 스크린 샷보기 :

3. 에서 워크 시트 결합 - 2의 3 단계 대화 상자에서 추가 > 필렛 or 폴더 병합 할 Excel 파일을 하나에 추가하십시오. Excel 파일을 추가 한 후 버튼을 클릭하고 마스터 통합 문서를 저장할 폴더를 선택하십시오. 스크린 샷보기 :

이제 모든 통합 문서가 하나로 병합됩니다.

위의 두 가지 방법과 비교하여, Excel 용 Kutools 다음과 같은 이점이 있습니다.

  • 1) 모든 워크 북과 워크 시트가 대화 상자에 나열됩니다.
  • 2) 병합에서 제외 할 워크 시트의 경우 선택을 취소하십시오.
  • 3) 빈 워크 시트는 자동으로 제외됩니다.
  • 4) 원본 파일 이름은 병합 후에 시트 이름에 접두사로 추가됩니다.
  • 이 기능의 더 많은 기능을 보려면, 여기를 방문하십시오..

팁. 이 유틸리티의 무료 평가판 (60-day)을 원한다면, 그것을 다운로드하려면 클릭하십시오, 위의 단계에 따라 작업을 적용하십시오.


Excel 용 Kutools - 시간을 앞두고 항상 일을 끝내고 인생을 즐기는 데 더 많은 시간을 할애 해줍니다.
자신과 가족을 위해 보내는 시간이 부족하고 일을 따라 잡는 것을 자주 보십니까? Excel 용 Kutools 80 % Excel 퍼즐을 다루고 80 % 업무 효율성을 개선하고 가족을 돌보며 삶을 즐기는 데 더 많은 시간을 할애 할 수 있습니다.
300 작업 시나리오를위한 1500 고급 도구를 사용하면 훨씬 쉽게 작업을 수행 할 수 있습니다.
더 이상 수식과 VBA 코드를 기억할 필요가 없으므로 이제부터는 뇌를 편안하게 쉬십시오.
복잡하고 반복되는 작업은 1 초 단위로 처리 할 수 ​​있습니다.
매일 수천 건의 키보드 및 마우스 작동을 줄이고 이제 직업병에 대한 작별 인사를하십시오.
3 분에 Excel 전문가가되어 신속하게 인정 받고 유료 인상 프로모션을 진행할 수 있습니다.
110,000은 매우 효과적인 사람들과 300 + 세계적으로 유명한 기업의 선택입니다.
다른 사람들에 대한 $ 39.0 이상의 가치가있는 $ 4000.0을 만드십시오.
모든 기능 무료 평가판 30 일. 이유없이 60-Day 환불 보증.

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Ltrung · 4 days ago
    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!
  • To post as a guest, your comment is unpublished.
    Gregory Dorvil · 1 months ago
    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?
  • To post as a guest, your comment is unpublished.
    shashank · 6 months ago
    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..
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Treb · 7 months ago
    Tanx for this, it helps me a lot... looking forward for more help from you. God bless you always.
  • To post as a guest, your comment is unpublished.
    Dezignext Technologies · 8 months ago
    I like using this technique better than using traditional "3D Formula" techniques in Excel.
  • To post as a guest, your comment is unpublished.
    Jonel · 10 months ago
    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,
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      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
  • To post as a guest, your comment is unpublished.
    Chris · 11 months ago
    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?
    • To post as a guest, your comment is unpublished.
      crystal · 8 months ago
      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
  • To post as a guest, your comment is unpublished.
    Owen · 1 years ago
    It didnt work for me then I realized my files are .xlsx, so added the missing "x" to the Filename line.
  • To post as a guest, your comment is unpublished.
    Justin · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Simona Pandele · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Please make sure you have put "\" at the end of your path. Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    janu · 1 years ago
    code not worked can anyone help me
  • To post as a guest, your comment is unpublished.
    Daniela · 1 years ago
    Big thanks! It was just what I needed
  • To post as a guest, your comment is unpublished.
    Gavin · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Nahid · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Tanner Markley · 1 years ago
    Thank you for providing the VBA code!
  • To post as a guest, your comment is unpublished.
    oduff · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Snow · 1 years ago
    Thank you for the VB code it has helped me with my job to make it easy.
  • To post as a guest, your comment is unpublished.
    James Gibson · 2 years ago
    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)?
  • To post as a guest, your comment is unpublished.
    Adam Grise · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Adam Grise · 2 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Adam Grise · 2 years ago
      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.
  • To post as a guest, your comment is unpublished.
    jay · 2 years ago
    Run-time error '1004':
    Copy method of worksheet class failed
  • To post as a guest, your comment is unpublished.
    kevin · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      Emile Keyser · 1 years ago
      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!
  • To post as a guest, your comment is unpublished.
    Hitesh · 2 years ago
    i want to combine data from multiple work books (excel file) whc includes 8 sheets
  • To post as a guest, your comment is unpublished.
    Lawrance · 2 years ago
    Error Line: Workbooks.Open Filename:=
  • To post as a guest, your comment is unpublished.
    ibra · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Shuk · 2 years ago
    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.
    • To post as a guest, your comment is unpublished.
      shuk · 2 years ago
      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
  • To post as a guest, your comment is unpublished.
    Shuk · 2 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.
    Johnny Bravo · 2 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 · 2 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.
      Lawrance · 2 years ago
      Hi,


      When execute the above script Workbooks.Open Filename:= shows error expected statment. Could you please helpw me to resolve the issue
    • To post as a guest, your comment is unpublished.
      Lawrance · 2 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Ma Hi · 2 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.
    E nic · 2 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.
    Jan Drozd · 2 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 · 2 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 · 2 years ago
    THANKS FOR THIS VALUABLE FORMULA....
    • To post as a guest, your comment is unpublished.
      Mohideen · 2 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.[quote name="kannan"]THANKS FOR THIS VALUABLE FORMULA....[/quote]
  • To post as a guest, your comment is unpublished.
    pat · 2 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 3 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 · 3 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 · 3 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 · 3 years ago
    GREAT STUFF!! You saved me a lot of effort!
  • To post as a guest, your comment is unpublished.
    Summer · 3 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 · 3 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 · 3 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 · 4 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 · 4 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.