How do I get a cell to read sheet label?

[VBailey]

I'm working on a report card for my mom's school, and I need to set up a
Macro Button that will Copy the test of that sheet's label & paste it into a
specific cell. This is what I recorded, but I'm getting an error at "Active
Sheet.Paste":

Range("R2:W2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Vanessa Bailey").Select
Sheets("Vanessa Bailey").Name = "Vanessa Bailey"
Range("R2").Select
ActiveSheet.Paste
Range("R2:W2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
End Sub

 

[Paul B]

VBailey, do you just need the sheet name in a cell? If so try something like
this
Range("A1") = ActiveSheet.Name
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003