worksheet help

[Excelnoobisme]

Hi,

Right now im using the following code to open all the files in a certain folder and copy the data into a spreadsheet.

Dim path As Variant
Dim excelfile As Variant
path = "C:\Program Files\Data"
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Application.DisplayAlerts = False

excelfile = Dir(path & "*.xlsm")

Do While excelfile <> ""
Workbooks.Open Filename:=path & excelfile
Set wb2 = ActiveWorkbook
excelfile = Dir
Sheets("Summary").Select
Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Windows("Test").Activate
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
wb2.Close savechanges:=False
Loop

how can i change to make it copy each files into different worksheet instead of 1 worksheet?

 

[Excelnoobisme]

any1 can help me pls. I wish to have it copy into different worksheets depend on how many file there is in the folder.

 

[John Davis]

any1 can help me pls. I wish to have it copy into different worksheets depend on how many file there is in the folder.

This should be tested first.

Sub excelnoobisme()
Dim path As Variant
Dim excelfile As Variant
Dim i As Long
path = "C:\Program Files\Data"
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Application.DisplayAlerts = False

excelfile = Dir(path & "*.xlsm")
i = 1
Do While excelfile <> ""
Workbooks.Open Filename:=path & excelfile
Set wb2 = ActiveWorkbook
excelfile = Dir
Sheets("Summary").Select
Range("A3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Windows("Test").Activate
Worksheets.Add Before:=Sheets("Sheet1")
ActiveSheet.Name = "New Sheet" & i
Range("A1").Offset(1, 0).PasteSpecial xlPasteValues
wb2.Close savechanges:=False
i = i + 1
Loop

End Sub