VBA Copy excel sheets to master sheet

Hi, i have 100 .xls files in a folder. Each excel has 1-2 sheets in it. I want to create a master excel file in the same location with all the 100 excel files (along with its respective tabs) one after the other in it . is there any vba code for it?

Top Contributor Asked on September 9, 2016 in VBA.
Add Comment
4 Answer(s)

Hi Chaitanya,

How are you! Here is the working example for consolidating the all worksheets from all workbooks in a folder to a maser workbook:

Copy All worksheet from All workbooks in a Folder to a Master Workbook


Sub sbCopyAllSheetsIntoMasterWorkbook()
'Variable Declarartion
Dim strFile As String
Dim strFolder As String
Dim wbMB As Workbook
Dim wbTB As Workbook
'Create objects
Set xlApp = New Excel.Application
Set wbMB = xlApp.Workbooks.Add
'Add attachment
strFolder = Range("B1")
'For Each excel File in the Folder
strFile = Dir(strFolder & "*.xls")
Do While Len(strFile) > 0
Set wbTB = xlApp.Workbooks.Open(strFolder & "\" & strFile)
' MsgBox strFile
For Each sht In wbTB.Sheets
sht.Copy after:=wbMB.Sheets(wbMB.Sheets.Count)
Set wbTB = Nothing
strFile = Dir
wbMB.SaveAs Filename:=strFolder & Range("B2") & ".xlsx"
Set wbMB = Nothing
Set xlApp = Nothing
MsgBox "Done"
End Sub


Here is the working Example –  VBA Macro File:




Keymaster Answered on September 9, 2016.
Add Comment

Hi Pnrao, this code is not working…when i download the file and give tle location of the file and click on execute it shows an errror. Could you look into this?

Top Contributor Answered on September 9, 2016.
Add Comment

Can you send me the modified file.

Keymaster Answered on September 9, 2016.
Add Comment

these are the steps i followed:

  • downloaded the file
  • changed the source location in cell b2
  • executed it . it just shows done and not the master file with consoldated files in it
Top Contributor Answered on September 9, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.