Note: The other languages of the website are Google-translated. Back to English
ログイン  \/ 
x
or
x
今すぐ登録  \/ 
x

or

Excelで複数のブックをXNUMXつのマスターブックに結合するにはどうすればよいですか?

複数のブックをExcelでマスターブックに結合する必要があるときに行き詰まったことがありますか? 最もひどいことは、組み合わせる必要のあるワークブックに複数のワークシートが含まれていることです。 そして、複数のワークブックの指定されたワークシートのみをXNUMXつのワークブックに結合する方法は? このチュートリアルでは、問題を段階的に解決するのに役立ついくつかの便利な方法を示します。


移動またはコピー機能を使用して、複数のワークブックをXNUMXつのワークブックに結合します

結合する必要のあるブックがXNUMXつしかない場合は、[移動]または[コピー]コマンドを使用して、ワークシートを元のブックからマスターブックに手動で移動またはコピーできます。

1.マスターワークブックにマージするワークブックを開きます。

2.マスターワークブックに移動またはコピーする元のワークブックのワークシートを選択します。

注意:

1)。 を押したまま、隣接していない複数のワークシートを選択できます。 Ctrlキー キーを押して、シートタブをXNUMXつずつクリックします。

2)。 複数の隣接するワークシートを選択するには、最初のシートタブをクリックして、 シフト キーを押し、最後のシートタブをクリックしてすべてを選択します。

3)。 任意のシートタブを右クリックして、をクリックすることができます すべてのシートを選択 コンテキストメニューから、ワークブック内のすべてのワークシートを同時に選択します。

3.必要なワークシートを選択したら、[シート]タブを右クリックし、[ 移動またはコピー コンテキストメニューから。 スクリーンショットを参照してください:

4.次に、 移動またはコピー ダイアログがポップアップします。 予約する ドロップダウンで、ワークシートを移動またはコピーするマスターワークブックを選択します。 で終了する移動を選択します シート前 ボックス、チェックボックス コピーを作成する ボックスをクリックし、最後に OK ボタン。

次に、XNUMXつのワークブックのワークシートをXNUMXつにまとめて表示できます。 上記の手順を繰り返して、ワークシートを他のワークブックからマスターワークブックに移動してください。


複数のワークブックまたは指定したシートのワークブックをVBAを使用してマスターワークブックに結合します

複数のワークブックをXNUMXつにマージする必要がある場合は、次のVBAコードを適用してすばやく実現できます。 次のようにしてください。

1.結合するすべてのワークブックを同じディレクトリの下にXNUMXつにまとめます。

2. Excelファイルを起動します(このワークブックがマスターワークブックになります)。

3。 プレス 他の + F11 キーを押して アプリケーション用のMicrosoftVisual Basic 窓。 の中に アプリケーション用のMicrosoftVisual Basic ウィンドウ、クリック インセット > モジュール、次に以下のVBAコードをモジュールウィンドウにコピーします。

VBAコード1:複数のExcelブックをXNUMXつにマージする

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
	

注意:

1.上記のVBAコードは、マージ後も元のワークブックのシート名を保持します。

2.マスターワークブックのどのワークシートがマージ後にどこから来たのかを区別したい場合は、以下のVBAコード2を適用してください。

3.ワークブックの指定されたワークシートをマスターワークブックに結合するだけの場合は、以下のVBAコード3が役立ちます。

VBAコードでは、「C:\ Users \ DT168 \ Desktop \ KTE \」はフォルダパスです。 VBAコード3では、 "Sheet1、Sheet3"は、マスターワークブックに結合するワークブックの指定されたワークシートです。必要に応じて変更できます。

VBAコード2:ワークブックをXNUMXつにマージします(各ワークシートには、元のファイル名のプレフィックスが付いた名前が付けられます):

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 コードを実行するためのキー。 次に、特定のフォルダー内のワークブックのすべてのワークシートまたは指定されたワークシートが、マスターワークブックに一度に結合されます。


複数のワークブックまたは指定したシートのワークブックをXNUMXつのワークブックに簡単に組み合わせる

幸いにも、 組み合わせる のワークブックユーティリティ Kutools for Excel 複数のワークブックをXNUMXつにマージするのがはるかに簡単になります。 複数のブックを組み合わせてこの関数を機能させる方法を見てみましょう。

申請する前に Kutools for Excelについては 最初にダウンロードしてインストールします.

1.新しいワークブックを作成し、をクリックします Kutools Plus > 組み合わせる。 次に、結合されたすべてのブックを保存する必要があり、保護されたブックに機能を適用できないことを通知するダイアログが表示されます。をクリックしてください。 OK ボタン。

2。 の中に ワークシートを組み合わせる ウィザード、選択 ワークブックの複数のワークシートをXNUMXつのワークブックに結合します オプションをクリックしてから、 ボタン。 スクリーンショットを参照してください:

3。 の中に ワークシートを組み合わせる-ステップ2/3 ダイアログボックスで 追加 > フィレット or フォルダ Excelファイルを追加するには、XNUMXつにマージします。 Excelファイルを追加したら、をクリックします。 終了 ボタンをクリックし、マスターワークブックを保存するフォルダーを選択します。 スクリーンショットを参照してください:

これで、すべてのワークブックがXNUMXつにマージされました。

上記のXNUMXつの方法と比較すると、 Kutools for Excel 次の利点があります。

  • 1)すべてのワークブックとワークシートがダイアログボックスに一覧表示されます。
  • 2)マージから除外するワークシートについては、チェックを外してください。
  • 3)空白のワークシートは自動的に除外されます。
  • 4)マージ後、元のファイル名がシート名のプレフィックスとして追加されます。
  • この機能のその他の機能については、 こちらをご覧ください.

  このユーティリティの無料トライアル(30日)をご希望の場合は、 クリックしてダウンロードしてください、次に、上記の手順に従って操作を適用します。


Excel用Kutools- あなたが常に前もって仕事を終えて、人生を楽しむためにより多くの時間を持っているのを助けます
仕事に追いついたり、自分や家族のために過ごす時間がないことはよくありますか?  Kutools for Excel 80%のExcelパズルに対処し、80%の作業効率を向上させ、家族の世話をして人生を楽しむためのより多くの時間を与えることができます。
300の作業シナリオに対応する1500の高度なツールにより、作業がこれまでになく簡単になります。
数式やVBAコードを覚える必要がなくなったので、これからは脳を休ませてください。
複雑で繰り返される操作は、数秒でXNUMX回の処理で実行できます。
毎日何千ものキーボードとマウスの操作を減らし、今は職業病に別れを告げましょう。
3分でExcelのエキスパートになり、すぐに認知され、昇給のプロモーションを手伝ってください。
110,000万人の非常に効果的な人々と300以上の世界的に有名な企業の選択。
あなたの$ 39.0を他の人の$ 4000.0以上のトレーニングに値するようにしてください。
30日間のフル機能の無料トライアル。 理由のない60日間の返金保証。

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.
    venkatesh · 3 months ago
    hai sir i want know code for copying multiple sheets in one excel to multiple excels
  • To post as a guest, your comment is unpublished.
    venkatesh · 3 months ago
    hai sir i want know code for copying multiple sheets in one excel to multiple excels
  • To post as a guest, your comment is unpublished.
    noelle · 6 months ago
    how to use VBA code 1 to amend and make it runs to combine all the .xlsx files into one excel file and each excel spreedsheet tab in the combined excel file should be named as original file name.thanks
  • To post as a guest, your comment is unpublished.
    Steve · 7 months ago
    What part of VBA code 3 specifies the name of the worksheet to be copied?

  • To post as a guest, your comment is unpublished.
    crystal · 8 months ago
    @Rajni bala Good day,
    I recommend the first option "Combine multiple worksheets from workbooks into one worksheet" of the Combine feature in Kutools for Excel to handle this work.
    You can open the below hyperlink for more details of this option:
    https://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html#a1
  • To post as a guest, your comment is unpublished.
    Rajni bala · 8 months ago
    sir i want to compile different worksheets from different workbook into one master sheet
  • To post as a guest, your comment is unpublished.
    Rangaswamy · 9 months ago
    Hi, While combining the worksheets from different workbooks, I want it to be paste special values. How to do that?
  • To post as a guest, your comment is unpublished.
    RameshPK · 11 months ago
    Hello Sir,
    When I run the VBA code 1, as per the instructions given. The script ran without any errors. but I could see in the master file
    1) each xls file from the directory got created 2 times in the master workbook.
    2) if master file name falls in between the other sheet names (ex: file1.xls, file2.xls, file3_master.xls, file4.xls ... ) then file4.xls is not being added into the file3_master.xls file.
    file1, file2 got created 2 times...

    Please help how to address this ?

    Thank you,
    Ramesh
  • To post as a guest, your comment is unpublished.
    Rajesh Chintamaneni · 11 months ago
    @crystal Can we Tweak it to get only orginal sheet name?
  • To post as a guest, your comment is unpublished.
    Sippika Kwatra · 1 years ago
    In VBA 2 option my sheet names as Name.xlsx - Can we remove .xlsx?
  • To post as a guest, your comment is unpublished.
    Ai · 1 years ago
    hello, vba code 3 isn't worked with me. can anyone help me? i'm using excel 2016. the code hasn't error. but, it can't worked. thanks
  • To post as a guest, your comment is unpublished.
    ltrung · 1 years 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.
    Gerdy · 1 years 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.
    crystal · 2 years ago
    @shashank 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.
    shashank · 2 years 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.
    Treb · 2 years 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.
    giorgia.fattorini@gmail.com · 2 years ago
    @crystal Hi Crystal,
    how can I copy only the first sheet of each folder?
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @dezignextllc@gmail.com I’m glad I could help ^_^
  • To post as a guest, your comment is unpublished.
    crystal · 2 years ago
    @Chris 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.
    crystal · 2 years ago
    @Jonel 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.
    dezignextllc@gmail.com · 2 years 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 · 2 years 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.
    Chris · 2 years 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.
    Owen · 2 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.
    crystal · 2 years ago
    @Simona Pandele 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.
    Justin · 2 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 · 2 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.
    janu · 2 years ago
    code not worked can anyone help me
  • To post as a guest, your comment is unpublished.
    Daniela · 3 years ago
    Big thanks! It was just what I needed
  • To post as a guest, your comment is unpublished.
    dekeyser1976@gmail.com · 3 years ago
    @kevin 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.
    Gavin · 3 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.
    Muhammad · 3 years ago
    @Nahid Use the remove duplicates option in Excel 2016

    https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
  • To post as a guest, your comment is unpublished.
    Nahid · 3 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 · 3 years ago
    Thank you for providing the VBA code!
  • To post as a guest, your comment is unpublished.
    · 3 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 · 3 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 · 3 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.
    adamwgrise@gmail.com · 3 years ago
    @adamwgrise@gmail.com 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.
    adamwgrise@gmail.com · 3 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.
    adamwgrise@gmail.com · 3 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.
    jay · 3 years ago
    Run-time error '1004':
    Copy method of worksheet class failed
  • To post as a guest, your comment is unpublished.
    kevin · 3 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.
    Hitesh · 3 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 · 3 years ago
    @Kevin Coutts 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 · 3 years ago
    Error Line: Workbooks.Open Filename:=
  • To post as a guest, your comment is unpublished.
    Lawrance · 3 years ago
    @Kevin Coutts 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.
    ibra · 3 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.
    DS · 3 years ago
    @samuel Birch Thanks a lot. Your code worked well.
  • To post as a guest, your comment is unpublished.
    shuk · 3 years ago
    @Shuk 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 · 3 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.