Multiple file merge output incorrect.

[adamcfishman]

I posted this earlier "I am working on a project that will require me to extract data from over 300 workbooks, looking for a simplified solution for extracted the data and compiling onto one new file. All workbooks are located at C:\Documents and Settings\af91468\Desktop\Inspections and I need to extract data from cells B1 through H27 (large range due to merging) I actually need the data from these cells specifically though a range will also work (B1:B6 AND B22:B27) Also some of this data will be the result of a formula. I am attaching a sample of the workbook that I need to extract. Any help would be awesome" and i had thought i found the resolution but the output is not how i want it with the code below and help to change code to reflect the attachmentSample Export.xlsSample Import.xls

Sub MergeHorizontally()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceCcount As Long, FNum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim Cnum As Long, CalcMode As Long

    ' Change this to the path\folder location of the files.
    MyPath = "C:\Users\af91468.NAM\Desktop\Inspections"

    ' Add a slash at the end of path if needed.
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    ' If there are no Excel files in the folder, exit.
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill in the myFiles array with the list of Excel files in
    ' the search folder.
    FNum = 0
    Do While FilesInPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FilesInPath
        FilesInPath = Dir()
    Loop

    ' Change the application properties.
    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)
    Cnum = 1

    ' Loop through all of the files in the myFiles array.
    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
                Set sourceRange = mybook.Worksheets(1).Range("b1:b27")

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    ' If the source range uses all of the rows
                    ' then skip this file.
                    If sourceRange.Rows.Count >= BaseWks.Rows.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceCcount = sourceRange.Columns.Count

                    If Cnum + SourceCcount >= BaseWks.Columns.Count Then
                        MsgBox "There are not enough columns in the sheet."
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        ' Copy the file name in the first row.
                        With sourceRange
                            BaseWks.Cells(1, Cnum). _
                                    Resize(, .Columns.Count).Value = MyFiles(FNum)
                        End With

                        ' Set the destination range.
                        Set destrange = BaseWks.Cells(2, Cnum)

                        ' Copy the values from the source range
                        ' to the destination range.
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        Cnum = Cnum + SourceCcount
                    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

[arlu1201]

Firstly, please do not create duplicate threads. If you do not receive a reply, you can bump your thread.

Coming to your question, do you want all the data to be stored one below the other in column B of the summary file? Do you want the file name to also show?

[adamcfishman]

Appoligies, thought I had figured it out but was unsucessful. No need for file name. What i would like to see is the results by row. Like this:
Name Number Region Divison Area Start Date PL TE FC IM Comp Overall
Sample 1 1 1 1 1 1 1 1 1 1
sample 2 2 2 2 2 2 2 2 2 2 2

[arlu1201]

You want the data to be consolidated starting in column A or column B of your master file?

[adamcfishman]

You want the data to be consolidated starting in column A or column B of your master file?

Looking to start in Column A

[AB33]

I have tested this code on PC and works. The reason why you are having an issue is may be due to merge cells. You need to unmerge them.

Sub MergeAllWorkbooksron()
    Dim MyPath As String, FolderPath As String, MyFiles() As String, SourceRcount As Long, FNum As Long
    Dim SourceBoook As Workbook, BaseWks As Worksheet, Sourcerng As Range, destrng As Range, rnum As Long, CalcMode As Long
    
    MyPath = "C:\Users\af91468.NAM\Desktop\Inspections"
    If Right(MyPath, 1) <> "\" Then ' Add a slash at the end of the path if needed.
        MyPath = MyPath & "\"
    End If

    ' If there are no Excel files in the folder, exit.
    FolderPath = Dir(MyPath & "*.xl*")
    If FolderPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    ' Fill the myFiles array with the list of Excel files in the search folder.
    FNum = 0
    Do While FolderPath <> ""
        FNum = FNum + 1
        ReDim Preserve MyFiles(1 To FNum)
        MyFiles(FNum) = FolderPath
        FolderPath = Dir()
    Loop

    With Application   ' Set various application properties.
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ' Add a new workbook with one sheet.
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    ' Loop through all files in the myFiles array.
    If FNum > 0 Then
        For FNum = LBound(MyFiles) To UBound(MyFiles)
            Set SourceBoook = Nothing
            On Error Resume Next
            Set SourceBoook = Workbooks.Open(MyPath & MyFiles(FNum))
            On Error GoTo 0

            If Not SourceBoook Is Nothing Then
                On Error Resume Next

                ' Change this range to fit your own needs.
                With SourceBoook.Worksheets(1)
                    Set Sourcerng = .Range("b1:b27")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set Sourcerng = Nothing
                Else
                    ' If source range uses all columns then skip this file.
                    If Sourcerng.Columns.Count >= BaseWks.Columns.Count Then
                        Set Sourcerng = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not Sourcerng Is Nothing Then

                    SourceRcount = Sourcerng.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "There are not enough rows in the target worksheet."
                        BaseWks.Columns.AutoFit
                        SourceBoook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        ' Set the destination range.
                        Set destrng = BaseWks.Range("A" & rnum)

                        ' Copy the values from the source range
                        ' to the destination range.
                        With Sourcerng
                            Set destrng = destrng.Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrng.Value = Sourcerng.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                SourceBoook.Close savechanges:=False
            End If

        Next FNum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    ' Restore the application properties.
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

[adamcfishman]

Works perfect... Another question for you is with this same code can i add additional cells that are not in a range such as b1:b27 and f43 and l46 etc... Also is there a code to unmerge the cells in all of the files?

Thank you again, you have been a lifesaver...

[AB33]

Yes, you can, need to adjust, this line.

Set Sourcerng = .Range("b1:b27")

There are many ways of doing it. You can loop through certain columns(Although it may slow down the speed)
If you have a fixed range to copy, you can use for example Union

 Set Sourcerng = Union(.Range("b1:b27"), .Range("F43"), .Range("I46"))

It all depends on the format of your data.
To unmerge cells

cells.UnMerge

[adamcfishman]

Where would i add the code for unmergeing?

[AB33]

Try to change the line

With SourceBoook.Worksheets(1)
        .Cells.UnMerge
         Set Sourcerng = .Range("b1:b27")
End With

[adamcfishman]

I modified this:

With SourceBoook.Worksheets(1)
                    .Cells.UnMerge
                    Set Sourcerng = Union(.Range("b1:b6"), .Range("b43"), .Range("f43"), .Range("l46"), .Range("b63"), .Range("l66"), .Range("l67"), .Range("l68"), .Range("l69").Range("l71"), .Range("b86"), .Range("l89"))
                End With

It does not seem to be unmerging files as results for only b1:b6 are displayed and all results are in column A
File 1
b1
b2
b3
etc.
file 2
b1
b2
b3

should be

File 1 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
File 2 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
File 3 - b1 b2 b3 b4 b5 b6 b43 f43 l46 b63 l66 l67 l68 l69 l71 b86 l89
etc through file 426

[AB33]

adam,
Please use code tags with your code.
If there is nothing to unmerge, this will not affect running the code.
The reason for including this line is in case you have merged cells, the code may give you an error, so the code unmerges the cells before it is looping through the range, but if there are not merge cells, the code works as intended.

As to this line

Set Sourcerng = Union(.Range("b1:b6"), .Range("b43"), .Range("f43"), .Range("l46"), .Range("b63"), .Range("l66"), .Range("l67"), .Range("l68"), .Range("l69").Range("l71"), .Range("b86"), .Range("l89"))

Ohm!!
I gave you just an example. It will not work until you get the lower and upper boundary right as the code, which was written by Ron Deburin, uses an array to copy back the range.
I suggest you to use similar code, which does not have array. A code which gives you flexibility and you can also easily amend it yourself.