The Parent of a Worksheet is a Workbook, and a Workbook cannot be renamed.
The only way to change the name of a Workbook is to do a SaveAs to a new
file name. Thus, code like
Dim WS As Worksheet
Set WS = Worksheets(1)
WS.Parent.Name = "abc"
will fail. The Range object has a Parent property that returns the worksheet
containing the range, and that can be renamed:
Dim RR As Range
Set RR = Worksheets(1).Range("A1")
RR.Parent.Name = "New Name"
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Rick S." wrote in message
...
I know how to rename a worksheet in VBA, ***Sheets(i).Name = "Sheet" &
i"***,
but I can not seem to rename the parent name.
Using ***Sheets(i).Parent.Name*** errors out. (invalid assignment?)
If I create a worksheet it counts and adds to the parent name so I could
end
up with Sheet15(Sheet2) what I want is Sheet2(Sheet2).
Thank you in advance.
--
Regards
Rick
XP Pro
Office 2007