title: Copy multiple xls files data to single file using VBA
body: <p>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.</p>
  
  <p>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. i want second file data to the next column.</p>
  
  <p>Below is my code</p>
  
  <pre><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 &amp; "*.xls")
  '--------------------------------------------
  'OPEN EXCEL FILES
   Do While Len(Filename) &gt; 0  'IF NEXT FILE EXISTS THEN
      Set wbk = Workbooks.Open(Path &amp; Filename)
      Set wbk = ActiveWorkbook
       sheetname = ActiveSheet.Name
      wbk.Sheets(sheetname).Activate
  
  Lastrow = ActiveSheet.Range("A" &amp; 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" &amp; 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
  </code></pre>
  
  <p>plz help me......
  Thanks in Advance</p>