Consolidate Data in Excel from Multiple sheets Using VBA

Consolidate Data in Excel from Multiple Worksheets Using VBA

Suppose we have multiple sheets in a single workbook and each worksheet has data. We want to consolidate data into single sheet then we have two option for doing this task first we can consolidate data manually but it will take more time second we can consolidate data from VBA and it will do the task in quick time.

For the solution of this query, we can use For Each loop, Do While Loop, Do Until Loop, and For Next Loop but we will use For Next Loop.

We have to follow some steps to consolidate data into a single sheet

(1). First of all, we should disable Screen Updating and Display Alert. This may help to code run fast. This is not necessary but this is good practice for programming.

         Application.ScreenUpdating = False

         Application.DisplayAlerts = False

(2). After that, we should also change Calculation option automatic to Manual, if we do not use any function in excel sheet for calculation. This may also help to code run fast.

         Application.Calculation = xlCalculationManual

(3). Declare the variable as per task

         Dim Sheet_Number As Integer

         Dim Master_Data As Integer

         Dim Total_Sheets As Integer   

(4). We add a sheet in first place in a workbook from this code

         Sheets.Add before:=Sheets(1)

(5). We can rename the sheet as per our choice

         Sheets(1).Name = “Master”

(6). We find the total number of sheet of this workbook

         Total_Sheets = Sheets.Count

(7). We use FOR NEXT LOOP for this task

(8). We start the loop from the second number of sheet to the last number of sheet          

         For Sheet_Number = 2 To Total_Sheets

(9). We apply logic, if sheet number is 2 then we copy entire data and

        paste into Master sheet.

         If Sheet_Number = 2 Then

(10). We copy entire data of excel sheet

         Sheets(Sheet_Number).Range(“a1”).CurrentRegion.Copy

(11). Paste into Master Sheet  

         Sheets(“Master”).Range(“a1”)

(12). In Else Part, if the sheet number is greater than 2 then we copy

        the data without header and paste into the Master sheet.

         Else

(13). Find the last non-blank row of Master sheet.

         Master_Data = Sheets(1).Cells(Rows.Count, “a”).End(xlUp).Row + 1

(14). Copy the data without header of next sheet.

         Sheets(Sheet_Number).Range(“a1”).CurrentRegion.Offset(1, 0).Copy

(15). Paste data into Master sheet.

         Sheets(“Master”).Range(“a” & Master_Data)

(16). Close If Function by End IF

         End If

(17) Use Next keyword for next variable value

         Next Sheet_Number   

(18).After that, we should enable Screen Updating, Display Alert.

         Application.ScreenUpdating = True

         Application.DisplayAlerts = True

(19). After that, we should also change the Calculation option Manual to Automatic.

         Application.Calculation = xlCalculationAutomatic

 

Consolidate Data in Excel from Multiple sheets Using VBA

Consolidate Data in Excel from Multiple Worksheets Using VBA   

 

Here is full code for consolidate data into Master Sheet

Sub Create_MasterData()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Application.Calculation = xlCalculationManual

    Dim Sheet_Number As Integer

    Dim Master_Data As Integer

    Dim Total_Sheets As Integer  

    Sheets.Add before:=Sheets(1)

    Sheets(1).Name = “Master”

    Total_Sheets = Sheets.Count

    For Sheet_Number = 2 To Total_Sheets

        If Sheet_Number = 2 Then

            Sheets(Sheet_Number).Range(“a1”).CurrentRegion.Copy Sheets(“Master”).Range(“a1”)

        Else

            Master_Data = Sheets(1).Cells(Rows.Count, “a”).End(xlUp).Row + 1

            Sheets(Sheet_Number).Range(“a1”).CurrentRegion.Offset(1, 0).Copy Sheets(“Master”).Range(“a” & Master_Data)

        End If

    Next Sheet_Number

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

    Application.Calculation = xlCalculationAutomatic

    MsgBox “Done “

End Sub

Above code help us to combine data into single sheet from multiple worksheets.

Consolidate Data in Excel from Multiple sheets Using VBA

Consolidate Data in Excel from Multiple Worksheets Using VBA   

 

Download Workbook

How to split Data one excel sheet into multiple sheets

Leave a Reply

Your email address will not be published. Required fields are marked *