consolidate data from different sheets to a single sheet

[jazzD]

so im a newbie, i found this code online and tried running it but keep getting a compile error on the bold script below - any idea how to fix?

Sub Consolidate_Data_From_Different_Sheets_Into_Single_Sheet()
'Procedure to Consolidate all sheets in a workbook


On Error GoTo IfError


'1. Variables declaration
Dim Sht As Worksheet, DstSht As Worksheet
Dim LstRow As Long, LstCol As Long, DstRow As Long
Dim i As Integer, EnRange As String
Dim SrcRng As Range


'2. Disable Screen Updating - stop screen flickering
'   And Disable Events to avoid inturupted dialogs / popups
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With


'3. Delete the Consolidate_Data WorkSheet if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Consolidate_Data").Delete
Application.DisplayAlerts = True


'4. Add a new WorkSheet and name as 'Consolidate_Data'
With ActiveWorkbook
    Set DstSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    DstSht.Name = "Consolidate_Data"
End With


'5. Loop through each WorkSheet in the workbook and copy the data to the 'Consolidate_Data' WorkSheet
For Each Sht In ActiveWorkbook.Worksheets
    If Sht.Name <> DstSht.Name Then
       '5.1: Find the last row on the 'Consolidate_Data' sheet
[B]       DstRow = fn_LastRow(Sht) + 1[/B]
               
       '5.2: Find Input data range
       LstRow = fn_LastRow(Sht)
       LstCol = fn_LastColumn(Sht)
       EnRange = Sht.Cells(LstRow, LstCol).Address
       Set SrcRng = Sht.Range("A1:" & EnRange)
       
       '5.3: Check whether there are enough rows in the 'Consolidate_Data' Worksheet
        If DstRow + SrcRng.Rows.Count > DstSht.Rows.Count Then
            MsgBox "There are not enough rows to place the data in the Consolidate_Data worksheet."
            GoTo IfError
        End If
                
      '5.4: Copy data to the 'consolidated_data' WorkSheet
        SrcRng.Copy Destination:=DstSht.Range("A" & DstRow)
                
    End If


Next


IfError:


'6. Enable Screen Updating and Events
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With


End Sub

 

[JoeMo]

Perhaps you are missing the function:

fn_LastRow(Sht)

that's being called to establish the variable DstRow.

 

[DanteAmor]

On this page is the complete code and an example to download.

https://analysistabs.com/vba-code/excel-projects/consolidate-data-from-multiple-worksheets-row/

 

[jazzD]

On this page is the complete code and an example to download.

https://analysistabs.com/vba-code/excel-projects/consolidate-data-from-multiple-worksheets-row/

thats the one i copied.... maybe i did sometning wrong! will try again!!

 

[DanteAmor]

thats the one i copied.... maybe i did sometning wrong! will try again!!

Add these functions in the module

'In this example we are finding the last Row of specified Sheet
Function fn_LastRow(ByVal Sht As Worksheet)


    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow


End Function

'In this example we are finding the last column of specified Sheet
Function fn_LastColumn(ByVal Sht As Worksheet)


    Dim lastCol As Long
    lastCol = Sht.Cells.SpecialCells(xlLastCell).Column
    lCol = Sht.Cells.SpecialCells(xlLastCell).Column
    Do While Application.CountA(Sht.Columns(lCol)) = 0 And lCol <> 1
        lCol = lCol - 1
    Loop
    fn_LastColumn = lCol


End Function

 

[shilpa_b]

Hi,

I have 1 file with 4 sheets and all sheets have different data with few same headers and few are different.
I need a master sheet to be looped in all sheets and retrieve data based on header name. Please help me.

 

[DanteAmor]

Hi,

I have 1 file with 4 sheets and all sheets have different data with few same headers and few are different.
I need a master sheet to be looped in all sheets and retrieve data based on header name. Please help me.

I do not understand what do you need.
Forget the code, explain with examples and with real data what you have in the sheets and what you expect of result.