How to combine multiple Excel sheet into one Excel sheet using VBA

Question

I have multiple excel sheets. Using VBA, I want to combine them into a particular sheet. For example, combine Sheet1, Sheet4, Sheet5 into one sheet and combine Sheet2, Sheet3, Sheet6 will go into another sheet. Basically sheet name needs to be hardcoded while combining.

There aren't enough details here to answer, but without any posted code or a description of exactly what problem you're having, your question may be closed as "too broad" You could make a start by recording a macro while performing the consolidation steps manually, and trying to modify the resulting code. — Tim Williams
Also this question was already asked about 100 times here on Stack Overflow. Find them have a look how they solved it and adapt it to your situation. If you get stuck or errors come back with your code. — Pᴇʜ

Error 1004 Error 1004 · Accepted Answer · 2019-03-18T09:21:18

Modify the below code and try the below:

Option Explicit

Sub test()

    Dim ws As Worksheet, ws145 As Worksheet, ws236 As Worksheet
    Dim Lastrow As Long

    'Let as assume that data appears in sheet 1
    With ThisWorkbook

        'Set the results sheets
        Set ws145 = .Worksheets("Sheet7")
        Set ws236 = .Worksheets("Sheet8")

        'Loop sheets
        For Each ws In .Worksheets
            'Check sheet name
            If ws.Name = "Sheet1" Or ws.Name = "Sheet4" Or ws.Name = "Sheet5" Then
                Lastrow = ws.cells(ws.Rows.Count, "A").End(xlUp).Row
                Lastrow1 = ws145.cells(ws145.Rows.Count, "A").End(xlUp).Row
                'Copy Column A from row 1 to Lastrow
                ws.Range("A1:A" & Lastrow).Copy ws145.Range("A" & Lastrow1 + 1)
            ElseIf ws.Name = "Sheet2" Or ws.Name = "Sheet3" Or ws.Name = "Sheet6" Then
                Lastrow = ws.cells(ws.Rows.Count, "A").End(xlUp).Row
                Lastrow1 = ws236.cells(ws236.Rows.Count, "A").End(xlUp).Row
                'Copy Column A from row 1 to Lastrow
                ws.Range("A1:A" & Lastrow).Copy ws236.Range("A" & Lastrow1 + 1)
            End If

        Next ws

    End With

End Sub

How to combine multiple Excel sheet into one Excel sheet using VBA

1 Answers