Renaming a worksheet in excel

[Sudhir Amin]

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.

 

[Bob Phillips]

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.Worksheets.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)

 

[Gary''s Student]

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

 

[Sudhir Amin]

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??