Copy all data from multiple sheets to one new sheet using VBA

Closed
@mar12
Posts
1
Registration date
Wednesday June 22, 2016
Status
Member
Last seen
June 22, 2016
- Jun 22, 2016 at 12:41 AM
Hello,
I have Multiple files in a folder.i wants to copy all Files data (i.e.all columns to new sheet) to one new sheet.
E.g. file 1 Contains 5 columns of data and file 2 contains 10 columns of data and so on. this data should copy on new sheet like first 5 columns are from file 1 and then on the same sheet from column 6, the file2 data should be copy and so on.

i tried but facing some problems like i am able to copy first file data successfully but when i am going to second file , second file data is overwriting on first file.

Below is my code

Public Sub CommandButton1_Click()
'DECLARE AND SET VARIABLES
Dim wbk As Workbook
Dim Filename As String
Dim Path As String
Dim mainwb As Workbook
Dim ws As Worksheet
Dim search_result As Range 'range search result
Dim blank_cell As Long
Dim wb As Workbook
Path = "C:\Test\"
Filename = Dir(Path & "*.xls")
'--------------------------------------------
'OPEN EXCEL FILES
Do While Len(Filename) > 0 'IF NEXT FILE EXISTS THEN
Set wbk = Workbooks.Open(Path & Filename)
Set wbk = ActiveWorkbook
sheetname = ActiveSheet.Name
wbk.Sheets(sheetname).Activate

Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To Lastrow

wbk.Sheets(sheetname).UsedRange.Copy

Workbooks("aaa.xlsm").Activate
Set wb = ActiveWorkbook
sheetname1 = ActiveSheet.Name
Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
wb.Sheets(sheetname1).Range("A1").Select
wb.Sheets(sheetname1).Paste
Next i
ActiveCell.Offset(0, 1).Select

wbk.Close SaveChanges:=False
Filename = Dir
Loop
End Sub

please help me......
Thanks in Advance