Thursday, 13 September 2012

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

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'
'MAIN SHEET NOT INCLUDE IN CONSOLIDATE SHEETS'
For x = 2 To 100 'ROWS HAVING THE DATA’
b = b + 1 'DATA 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 DEVIDED BY   A SINGLE EMPTY ROW
End If

Next a

End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts