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

"brandyvine" wrote:

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.
--
b