Transfer different workbooks into one keeping the cells format

[PsychSilvia]

I am new to the VBA language and coding. I have a folder with 51 Excel files. In each file there are specific variables between cell J4 and cell R4, included, that I want to tranfer into an inclusive excel workbook. The code that I am using is the following:

<code>Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\Users\Aaa\Desktop\Analysed Data")

Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("J4:R4").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow,1),Cells(erow,51))

MyFile = Dir
Loop
End Sub
</code> Mainly this code does what I want: it transfer the range of values of my 51 different files into a new workbook. The format however for some values derived from formulas in the original files is messed up. I have tried to change my paste line as following:

ActiveSheet.PasteSpecial xlPasteValuesFormat Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 51))

But it is not working and I get the following error: "Compile error:expected:end of statement"
Any ideas on how I can solve my problem? Thank you.

 

[pbornemeier]

When you close the source workbook before pasting the data you copied, you lose the 'PasteSpecial' options.
The order that Dir returns a filename is not fixed. If you exit the Do...Loop when zmaster.xlsm comes up you might not process some data files.
Do you need to know which line came from which workbook?
Is there only 1 sheet in each source workbook? (or can you be sure that the sheet containing the data is the one that was active when each workbook was saved?)
You are copying 1 row of 9 columns and pasting 1 row of 51 columns. For the paste you only need to specify the upper left cell of the destination range.
There are many options for PasteSpecial, xlPasteValuesFormat is not one of them. I believe you want: xlPasteValuesAndNumberFormats

Keeping the above in mind, see if this does what you want:

Option Explicit

Sub LoopThroughDirectoryMod()
    
    Dim MyFile As String
    Dim erow
    Dim sFilePath As String
    
    sFilePath = "C:\Users\Aaa\Desktop\Analysed Data\"  'added trailing slash
    MyFile = Dir(sFilePath)
    
    sFilePath = "C:\Users\philip.bornemeier\Documents\-- Excel Processing\MrE\"
    MyFile = Dir(sFilePath)
    MyFile = "book1.xlsx"
    
    Do While Len(MyFile) > 0
    
        If MyFile <> "zmaster.xlsm" Then
        
            Workbooks.Open (MyFile)
            'Uncomment next row if you want to always get data from the first worksheet 
            'Worksheets(1).Activate  'Activates the leftmost sheet in the just-opened workbook
            Range("J4:R4").Copy
            
            erow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            
            With ThisWorkbook.Worksheets("Sheet1").Cells(erow, 1)
                .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
                    xlNone, SkipBlanks:=False, Transpose:=False
                'If you want the source of each row in column 12 of its row, uncomment the next line
                '.Offset(0, 12).Value = MyFile
            End With
            ActiveWorkbook.Close
            'ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 51))
            
            MyFile = Dir
            
        End If
        
    Loop

End Sub