Consolidate From Sheets

Below Code helps to consolidate sheets into One sheet

Function Consolidate_From_Sheets()
    'Macro Number :: 112 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Conslolidate data from multiple Sheet to One Sheet ACtiveworkbook
    Dim mSht As Worksheet  ' Master Sheet
    Dim dSht As Worksheet
    Dim mRw As Long  ' Master Sheet Row Count
    Dim tRw As Long
    Dim mshtName As String
    mshtName = "EMC_Master_Data"

    With ActiveWorkbook
        On Error Resume Next
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        On Error GoTo 0
        Set mSht = .Sheets.Add
        mSht.Name = (mshtName)
        For Each dSht In .Sheets
            If dSht.Name = mSht.Name Then GoTo nextsheet
            mRw = Application.CountA(mSht.Columns(2)) + 1
            dSht.Range("A1").CurrentRegion.Copy mSht.Range("B" & mRw)
            mSht.Range("A" & mRw) = dSht.Name
        mRw = Application.CountA(mSht.Columns(2))
        mSht.Range("A1:A" & mRw).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        mSht.Range("A1:A" & mRw).Value = mSht.Range("A1:A" & mRw).Value
    End With
    Set dSht = Nothing
    Set mSht = Nothing
End Function

Leave a Reply