No you didn't remove the comma, because it is still there in the line in yellow.
Delete al the code and put the following code in again, this time I corrected it:
Code:
Option Explicit
' >>>>> Put the initial path where the files to be processed are stored here. _
End with backslash
Const sInitialPath = "C:\MyPath\"
Sub GetData()
Dim wbIn As Workbook, wbOut As Workbook
Dim rIn As Range, rOut As Range
Dim wsIn As Worksheet, wsOut As Worksheet
Dim diaFolder As FileDialog
Dim lCount As Long
Set wbOut = ThisWorkbook
' Assuming masterWB has only one sheet
Set wsOut = wbOut.Sheets(1)
' get file name for file to process
MsgBox "Select all the files you want to process by using the Ctrl key and the mouse. "
' Open the file dialog to get the files
Set diaFolder = Application.FileDialog(msoFileDialogFilePicker)
With diaFolder
.AllowMultiSelect = True
.InitialView = msoFileDialogViewList
.InitialFileName = sInitialPath
lCount = .Show
End With
If lCount = -1 Then
' for each selected file
For lCount = 1 To diaFolder.SelectedItems.Count
Set wbIn = Workbooks.Open(diaFolder.SelectedItems(lCount))
'loop through all the sheets in the opened book
For Each wsIn In wbIn.Sheets
'set output range on the Mastersheet to last row
Set rOut = wsOut.Cells(1,wsOut.Columns.Count,).End(xlLeft).Offset(0,1)
'now copy the values accross to the Mastersheet
With wsIn.Range("A1:C60")
rOut.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
Next wsIn
'close WB
wbIn.Close savechanges:=False
Next lCount
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
'Cleanup
Set wbIn = Nothing
Set wbOut = Nothing
Set rIn = Nothing
Set rOut = Nothing
Set wsIn = Nothing
Set wsOut = Nothing
Set diaFolder = Nothing
End Sub
This line is not pasting the data. What it does is it sets the range (area in the worksheet) where the values are to be copied to to the right of the last column.
It works as follows:
rOut is declared as a range. In order to set a range I use the word Set
Set rOut = Range("A1")
will set rOut to the same spot as A1
But I want to set it to the last column, one right actually. So I start from the very last column in the workbook on row 1. This is cell XFD1. Then I tell Excel to go to the End Left (just as you would do with End Left-arrow) so it stops at the last column in use in row 1. Then I tell it to take one step to the right (.Offset(0,1)).
However to avoid issues with versions of Excel, I dont use XFD1 as the starting point, because Excel 1997 has IV1 as the last column, and some futere version may have something different. So I ask Excel what the last column number is (Columns.Count). And to use this it is easier to use the term Cells() rather than Range(). So Cells(3,1) is the same as Range("A3") . and Cells(1, Columns.Count) is the same as Range("XFD1").
After I have set my output range in this way I then set the values of the output range to the same values as the input range. This is far, far faster than using copy/paste.
