Merging all Workbooks in a Folder copies from the first workbook only,but not all

[AB33]

I think this code is written by Ron. I have adjusted some of the lines to suite my needs. The code loops correctly through all files on the folder, but copies only the range from the first book. It loops through the rest of the folder, but does not copy anything. I have range values in most workbooks, but the code does not copy. My books have a mixture of file extenstions, xlsx, xlsm.

Any idea, or suggestion would be much appreciated!

ub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet, FolderPath As String
    Dim NR As Long, FileName As String, WorkBk As Workbook, SourceRange As Range, DestRange As Range
    Application.ScreenUpdating = 0: Application.DisplayAlerts = 0: Application.EnableEvents = 0
    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    FolderPath = "C:\Marcotest\" ' Modify this folder path to point to the files you want to use.
        NR = 1
    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")
    Do While FileName <> ""  ' Loop until Dir returns an empty string.
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)
            ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NR).Value = FileName
        ' Set the source range to be A9 through C9.Modify this range for your workbooks.
        Set SourceRange = WorkBk.Worksheets(1).Range("A1:Z1000")
        ' Set the destination range to start at column B and be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NR)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)
           ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
       
        NR = NR + DestRange.Rows.Count ' Increase NR so that we know where to copy data next.
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop
    SummarySheet.Columns.AutoFit ' Call AutoFit on the destination sheet so that all data is readable.
End Sub

[Richard Buttrey]

Try using

SourceRange.Copy
DestRange.Cells(1,1).PasteSpecial(xlPasteValues) ' or perhaps .PasteSpecial(xlPasteAll)

instead of
DestRange.Value = SourceRange.Value

[AB33]

Richard,

Thank you very much for your help!

Infact, I find out what was the issue. The code copies from the first book from Range(A1-1000) and then it correctly copies from the second book as well, but instead of copying in the next empty row, it copies starting from row 1001. My first book has only 9 rows (Range 1-9), so despite the range specified is 1-1000, I would have expected the next empty to be copied should in row 10, not 1001.

Your code helps me to see all rows up to 1000 have been highlighted when the code pastes.

As I had expected the code to copy in the nexy empty row which should be row 10, but the code was copying in row 1001, hence I did not go down to see the copied range below 1000. When I saw empty rows I thought the code was not working. In fact the code was copying below 1000 which I could not see.

Do you have an idea why the code behaves this way? Usedrange may be a solution.

[AB33]

Richard,
I am really sorry for addressing you with the wrong name. I have now corrected the thread.