CODEWITHSUNDEEP
excelvba
Tung Nguyen
Tung Nguyen

Reputation: 11

Merge sheets from multiple workbooks into one sheet

I have many excels with different sheets and same formats. Is there any plugin available to combine all sheets into a "Merged" sheet in the target file?

But the following code can't be merged into 1 sheet

Sub GopFileExcel()
Dim FilesToOpen
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="hMicrosoft Excel Files (*.xlsx), *.xlsx", 
MultiSelect:=True, Title:="Files to Merg")
If TypeName(FilesToOpen) = “Boolean” Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Upvotes: 1

Views: 311

Answers (2)

Isaac
Isaac

Reputation: 39

Following up on @Chrismas007 answer, after you have all the sheets in one workbook, you can use the below snippet to copy the wanted range to the desired target sheet. This will append your copied section to the next unused column in the targetsheet. 

Dim ws As Worksheets
Dim lastcol As Integer

For Each ws In Worksheets
   ThisWorkbook.Worksheets(ws).Column(copyrange).Copy
   lastcol = Worksheets(targetsheet).Cells(1, Columns.Count).End(xlToLeft).Column + 1
   ThisWorkbook.Worksheets(targetsheet).Cells(1, lastcol).PasteSpecial
 Paste:=xlPasteValues, operation:=xlNone
Next

Upvotes: 0

Chrismas007
Chrismas007

Reputation: 6105

The first thing to do would be to get all sheets into one workbook. Here is some code that could help with that.

Sub GetSheets()

Path = "C:\Users\USERNAME\Downloads\Test\" 'File path will all your separate files

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

Then you could write a loop to loop through all sheets and move the data into one sheet.

Upvotes: 1

Related Questions