And if statement is checking the Input sheet(Input Data) and destination sheet(Consolidated Sheet) is equal or not.
If it is equal then it is going to check next worksheet.
In this case, you may want to employ one of the following techniques to automate the merge.
how to work with validating couples - Consolidating excel worksheets
Count is the default function for source data other than numbers Average The average of the values. Std Dev An estimate of the standard deviation of a population, where the sample is all of the data to be summarized.
Std Devp The standard deviation of a population, where the population is all of the data to be summarized.
VBA to concatenate the data in multiple Worksheets to a newly created Worksheet in the same workbook.
The ranges in all worksheets are concatenated into the consolidated Worksheet(final Worksheet) one after another in rows wise. Enable Screen Updating and Events With Application . Enable Events = True End With End Sub Below are the two user defined functions which we have created to find the last row and last column of the given worksheet.
For the wizard to identify the columns correctly, make sure you have selected the option My tables have headers.
As the result, your Excel worksheets will be merged as demonstrated in the following screenshot. Merge certain columns from multiple sheets If you have really large sheets with tons of different columns, you may want to merge only the most important ones to a summary table.
To consolidate all worksheets in the workbook, we have to first create a new worksheet(lets call master sheet) and then loop through each worksheet in the workbook. Range("A1:" & En Range) '5.3: Check whether there are enough rows in the 'Consolidate_Data' Worksheet If Dst Row Src Rng. ‘fn_Last Row’ will accept a worksheet(Sht) as input and give the last row as output.
We have to find the valid data range in each worksheet and append to the newly created master sheet at the end of the row. Name Then '5.1: Find the last row on the 'Consolidate_Data' sheet Dst Row = fn_Last Row(Dst Sht) 1 '5.2: Find Input data range Lst Row = fn_Last Row(Sht) Lst Col = fn_Last Column(Sht) En Range = Sht. 'In this example we are finding the last Row of specified Sheet Function fn_Last Row(By Val Sht As Worksheet) Dim last Row As Long last Row = Sht.
To combine just a couple of sheets, you may not need anything else but the good old copy/paste.
But if you are to merge tens of sheets, errors with manual copying/pasting are inevitable.
If it is not equal to then it copies the input data and concatenate to Consolidated Worksheet.