Consolidating 1 sheet from multiple workbooks to 1 sheet in a master workbook.

baller1013

New Member
Joined
Sep 7, 2017
Messages
15
I have worksheets that added to a folder daily, and I would like to run the following code every day to add the sheet's data to the master worksheet's data. My problem is that instead of adding to the existing data on the worksheet titled "MuniReg" it overwrites the data, I believe I have identified the section of the code that needs altered (in bold), but I do not know how to alter it. I'm sure there is a way to use the RDB_Last function for this, but I am a novice. Your help is appreciated!!!

Code:
Sub Consolidate_Sheets_1()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim FirstCell As String
    
    
    'Fill in the path\folder where the files are
    MyPath = "N:\Will\Muni Registrations Received"
    'Add a slash at the end if the user forgot it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If
    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop
    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Add a new workbook with one sheet
    'Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    Set BaseWks = Worksheets("MuniReg")
    BaseWks.Activate
    
        
    [B]rnum = 2[/B]
    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            On Error GoTo 0
            If Not mybook Is Nothing Then
                On Error Resume Next
            With mybook.Worksheets("MuniReg")
                FirstCell = "A2"
                Set sourceRange = .Range(FirstCell & ":" & RDB_Last(3, .Cells))
                'Test if the row of the last cell >= then the row of the FirstCell
                If RDB_Last(1, .Cells) < .Range(FirstCell).Row Then
                     Set sourceRange = Nothing
                End If
            End With
                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0
                If Not sourceRange Is Nothing Then
                    SourceRcount = sourceRange.Rows.Count
                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else
                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(Fnum)
                        End With
                        'Set the destrange
                       [B] Set destrange = BaseWks.Range("B" & rnum)[/B]
                        'we copy the values from the sourceRange to the destrange
                        sourceRange.Copy
                    
                    With destrange
                        .PasteSpecial xlPasteValues
                        .PasteSpecial xlPasteFormats
                        Application.CutCopyMode = False
                    End With
                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If
        Next Fnum
        BaseWks.Columns.AutoFit
    End If
ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try
Code:
Sub Consolidate_Sheets_1()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim FirstCell As String
    
    
    'Fill in the path\folder where the files are
    MyPath = "N:\Will\Muni Registrations Received"
    'Add a slash at the end if the user forgot it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If
    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop
    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Add a new workbook with one sheet
    'Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    Set BaseWks = Worksheets("MuniReg")
    BaseWks.Activate
    
        
    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            On Error GoTo 0
            If Not mybook Is Nothing Then
                On Error Resume Next
            With mybook.Worksheets("MuniReg")
                FirstCell = "A2"
                Set sourceRange = .Range(FirstCell & ":" & RDB_Last(3, .Cells))
                'Test if the row of the last cell >= then the row of the FirstCell
                If RDB_Last(1, .Cells) < .Range(FirstCell).Row Then
                     Set sourceRange = Nothing
                End If
            End With
                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0
               [COLOR=#ff0000] rnum = BaseWks.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row[/COLOR]
                If Not sourceRange Is Nothing Then
                    SourceRcount = sourceRange.Rows.Count
                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else
                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.Cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(Fnum)
                        End With
                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)
                        'we copy the values from the sourceRange to the destrange
                        sourceRange.Copy
                    
                    With destrange
                        .PasteSpecial xlPasteValues
                        .PasteSpecial xlPasteFormats
                        Application.CutCopyMode = False
                    End With
                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If
        Next Fnum
        BaseWks.Columns.AutoFit
    End If
ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub
 
Upvote 0
Fluff- Thank you. However, it doesn't appear that the code is working as intended. For some reason, it is only picking up three files from the location and there are like 14 in the location. Which is strange.....
 
Upvote 0
If you run this, it will give you a list of the files found. Is that list correct?
Code:
Sub Consolidate_SheetsTest()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim FirstCell As String
    Dim msg As String
    
    
    'Fill in the path\folder where the files are
    MyPath = "N:\Will\Muni Registrations Received\"
    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If
    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
        msg = msg & FilesInPath & vbLf
    Loop
    MsgBox msg

End Sub
 
Upvote 0
That suggests that some of the files are either corrupt, or password protected.
Can you open them manually?
 
Upvote 0
I think I figured it out. Thanks Fluff!!
Code:
Set BaseWks = Worksheets("MuniReg")    BaseWks.Activate


rnum = BaseWks.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
 
Upvote 0
Glad you sorted it out & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top