Copy worsheet from different files in to one file

B

Boss

Hi,

The situation is, I have 25 excel files in my C:/ drive.

I need to copy first worksheet named as "Fullrecon" from all 25 excel files
into one excel file.

In the new file, the data from the 25 excel sheets should come in one single
worksheet.

In other words if each of 25 excel sheets contain 100 rows then the final
excel sheets should contain 2500 rows.

This task is very important for me. I am not so good at coding. Please help
me achieve this. Thanks a lot for the help.

Thanks
Boss
 
J

Joel

Sub combine()

Folder = "c:\temp"
First = True
Do
If First = True Then
FName = Dir(Folder & "\*.xls")
First = False
Else
FName = Dir()
End If
If FName <> "" Then
Workbooks.Open Filename:=Folder & "\" & FName
Set newbk = ActiveWorkbook

With ThisWorkbook.ActiveSheet
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With newbk.Sheets("Fullrecon")
NewLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & NewLastRow).Copy _
Destination:=ThisWorkbook.ActiveSheet.Rows(NewRow)
End With
newbk.Close
End If
Loop While FName <> ""
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top