macro renaming tab

[brandyvine]

Hi, I'm pretty new at macros, but I've written one that always has a
"current" tab. the current tab is copied and some changes are made to it and
the macro goes back to the current tab and copies the date range in a
specific cell on the worksheet to rename the tab with that cell's info. It
then goes to "current (2)" and renames it to "current". Problem is the macro
doesn't reference the cell info dynamically, instead it took the actual dates
and hard coded them into the macro. So when I go to run it the next week it
can't rename the new tab the same name as an existing tab (since it isn't
taking the new data from the new "current" sheet, its trying to name it the
hard coded info which has already been used to name a previous weeks tab.)
Here is the code as it exists. I tried changing it to reference the cell
directly but it just skipped over it and did not rename it at all.

Sheets("current").Select
ActiveWindow.SmallScroll Down:=-21
Range("A24").Select
Selection.Copy
Sheets("current").Select
Sheets("current").Name = "10-22-07 thru 11-04-07"
Sheets("current (2)").Select
Sheets("current (2)").Name = "current"

Thanks for any insight you can offer to help solve this issue.

 

[Luke M]

If I understand you right, the second sheet is always named "Current"? If so,
this should work:

Sub Rename()

application.screenupdating = false
Sheet1.select
Sheet1.Name = Range("A24").Value
Sheet2.select
Sheet2.Name = "current"

End sub

Note that the number in the names corresponds to the hardcode number of each
sheet. You can change this to match whichever sheets you are actually using.

 

[JLGWhiz]

Sheets("current").Activate
ActiveSheet.Name = Range("A24").Value
Sheets("current (2)").Name = "current"