Renaming a worksheet in excel
 

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.

Renaming a worksheet in excel
 

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.

Renaming a worksheet in excel
 

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.

Renaming a worksheet in excel
 

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.