How to Combine or Merge or Consolidate All Sheets Into One Sheet with VBA Macro

Excel VBA Macro To Combine or Merge or Consolidate All Sheets Into One Sheet

Sub ConsolAllSheets()

Dim x As Integer

Dim y As Integer

Dim a As Worksheet

Dim b As Integer

b = 1 'Increment Variable

For Each a In Worksheets

a.Activate

If a.Name <> Sheets("MAIN").Name Then

'Upto Main Sheet where we consolidate data, and the Main Sheet will now include in Consolidated sheets.
For x = 2 To 100 'Rows having the Data.

b = b + 1 'Data row begins from 2 Row in Consolidated Sheet.

For y = 1 To 6 'Columns having the data.

Sheets("MAIN").Cells(b, y) = a.Cells(x, y)

Next y

If Cells(x, 2) = "" Then Exit For

Next x

'b = b - 2 'Joins each Sheet data together, else each Sheet data is separated by a single empty Row.

End If

Next a

End Sub

Thanks,Tamatam