Collating data from multiple excels into a single excel (by pasting below as values)

[lubaibv]

Hi,

I am trying to figure out a way to collate data from multiple excels in a folder to a single master file by copy-pasting below in the master file. I tried running the below code, but i am getting error

Could someone help me with where i am wrong. I am new to VBA coding !

Sub Copy_data_from_market_files()

Dim FolderPath As String, Filepath As String, Filename As String
FolderPath = "C:\Users\Lubaib\Desktop\Market_files\"
Filepath = FolderPath & “ * .xlsx”
Filename = Dir(Filepath)
Dim erow As Long, lastrow As Long, lastcolumn As Long

Do While Filename <> “”

Dim wb As Workbook
Set wb = Workbooks.Open(FolderPath & Filename)

wb.“Sheet1”.Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy

‘ Sheets(“Sheet1”).Select
Thisworkbook."Sheet1".Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste

Next
wb.Close savechanges:=False

Filename = Dir

Loop

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select

End Sub

 

[vletm]

lubaibv
Where did You get error?

You could check those "Sheet1"s.
wb.“Sheet1”.Activate
Thisworkbook."Sheet1".Activate

Those would work better if You would use syntax as You have there
Sheets(“Sheet1”)

You also could test syntax
wb.sheets("Sheet1").range( your_source_range ).copy destination:= wb2.sheets("Sheet1").range( your_target_range )
((( wb2 is Your target file )))