View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Sean Sean is offline
external usenet poster
 
Posts: 454
Default Copy a Sheet to New Sheet Q

On Aug 20, 9:54 pm, "Zone" wrote:
Sean, I didn't realize A5 had a date in it. Try changing the
ActiveSheet.Name line to this:

ActiveSheet.Name = Format([a5], "dd-mm-yy")

That should fix the problem. James

"Sean" wrote in message

ps.com...



On Aug 20, 12:14 pm, "Zone" wrote:
Copy the code below and paste it in a standard module. HTH, James


Sub CopySht()
Dim shtName As String
shtName = ActiveSheet.Name
ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = [a5]
Sheets(shtName).Activate
End Sub


"Sean" wrote in message


roups.com...


How would I copy the active Sheet to a newly created Sheet, then
rename "New Sheet" to the value in A5 in the Active sheet, then return
the cursor to the active sheet?


I have essentially a workbook that builds up a new sheet each week
(name of new sheet would be in format dd/mm/yy)


Thanks- Hide quoted text -


- Show quoted text -


Thanks James, but I'm hitting debug. Also my value in A5 is in the
format DD/MM/YY, but your code creates a new sheet with the same name
as the 'Original' except an appendix eg Master(1), but I wish to have
the sheet created as 19-08-07, if A5=19/08/07 etc etc


Thanks- Hide quoted text -


- Show quoted text -


Spot on James. One final tweak. It places the new sheet at the end of
my sheet list, how would I place it just to the right of my original
sheet?

Thanks