I have about 10 different excel files in a folder. Each file has 1 worksheet
whose data i want to pull in different sheets in 1 consolidated excel sheet.

I do know how to pull the data from different sheets by recording a macro.
What i would like to know is how do i rename the 10 different sheets of the
consolidated excel sheet so that it matches the names of the different excel
files in the folder.

Sub ImportFiles()
Const DIR_PATH As String = "C:\test\" '<=== change to suit
Dim Filename As String
Dim WB As Workbook

Filename = Dir(DIR_PATH & "*.xls")

Do
If Filename < "" Then
Set WB = Workbooks.Open(DIR_PATH & Filename)

WB.Worksheets(1).Copy
After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)
WB.Close SaveChanges:=False
Filename = Dir
End If
Loop Until Filename = ""
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sudhir Amin" wrote in message
...
I have about 10 different excel files in a folder. Each file has 1
worksheet
whose data i want to pull in different sheets in 1 consolidated excel
sheet.

I do know how to pull the data from different sheets by recording a macro.
What i would like to know is how do i rename the 10 different sheets of
the
consolidated excel sheet so that it matches the names of the different
excel
files in the folder.

First put the names of the 10 source sheet files in column A of Sheet1 of the
consolidated worksheet and then run this small macro from that sheet:

Sub name_um()
For i = 1 To 10
Sheets(i).Name = Cells(i, 1).Value
Next
End Sub

--
Gary''s Student - gsnu200765


"Sudhir Amin" wrote:

I have about 10 different excel files in a folder. Each file has 1 worksheet
whose data i want to pull in different sheets in 1 consolidated excel sheet.

I do know how to pull the data from different sheets by recording a macro.
What i would like to know is how do i rename the 10 different sheets of the
consolidated excel sheet so that it matches the names of the different excel
files in the folder.

Thankls this worked pretty ok but not exactly what i was looking for. Instead
of manually entering the names of the files in column A of sheet 1, is there
any other way of the macro picking up the name of the file and automatically
entering it as the worksheet name??

"Gary''s Student" wrote:

First put the names of the 10 source sheet files in column A of Sheet1 of the
consolidated worksheet and then run this small macro from that sheet:

Sub name_um()
For i = 1 To 10
Sheets(i).Name = Cells(i, 1).Value
Next
End Sub

--
Gary''s Student - gsnu200765


"Sudhir Amin" wrote:

I have about 10 different excel files in a folder. Each file has 1 worksheet
whose data i want to pull in different sheets in 1 consolidated excel sheet.

I do know how to pull the data from different sheets by recording a macro.
What i would like to know is how do i rename the 10 different sheets of the
consolidated excel sheet so that it matches the names of the different excel
files in the folder.