search for variable worksheet name and rename
 

I am importing several text files into a workbook. Each file name becomes the
name of its worksheet. There is one file name, however, that is too long for
the worksheet and its sheet name reverts to the default Sheet4 (or whatever
number sheet it happens to be at). The number of files that get pulled in
will vary. I am linking Access tables to each sheet and I need to be able to
have that sheet's name be constant. There may also be more than one of each
type of file. Example:

Joe Joe1 Joe2 Robert Robert1 Sheet6 Sheet7

I would need Sheet6 and Sheet7 to read Darcy Darcy1

I have tried many of the suggestions offered in here and have even tried
combining several. I can get it to loop through all the sheets, but it
renames every sheet, not just the ones names Sheet*. I have even tried
searching a specific cell in each sheet, but to no avail.

If it makes a difference, I am using Office Excel 2003.

search for variable worksheet name and rename
 

Sub rnsheet()
For Each s In Sheets
If Left(s.Name, 5) = "Sheet" Then
s.Name = "larrydave" & i
i = i + 1
End If
Next
End Sub
--
Gary''s Student - gsnu200779

search for variable worksheet name and rename
 

You are beautiful!
I hadn't thought about checking the left side of the string.
Thank you so much!

"Gary''s Student" wrote:

Sub rnsheet()
For Each s In Sheets
If Left(s.Name, 5) = "Sheet" Then
s.Name = "larrydave" & i
i = i + 1
End If
Next
End Sub
--
Gary''s Student - gsnu200779

search for variable worksheet name and rename
 

You are very welcome.
--
Gary''s Student - gsnu200779