Rename Tab

[Kerry]

Hi Everyone,
By pressing one button I am trying to print out the timesheet, copy and save
as a new sheet and then rename the tab from the formula in cell A81. It is
hanging at ActiveSheet.Name = Range("A81").Value. Any suggestions would be
appreciated.

Sheets("Timesheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = Range("A81").Value
Sheets("Timesheet").Select

 

[Jacob Skaria]

Dont find any issue with your code. Make sure Range("A81") is not empty or a
sheet already exist by the name. Suggest you to print at last.


If this post helps click Yes

 

[OssieMac]

Couple more suggestions.
Worksheet name max 31 characters
None of these characters : \ / ? *

 

[Kerry]

Hi Jacob,
I put a number in another cell and referenced to that cell and it worked so
it appears that the problem is with cell A81. The formula in cell A81 is =A80
and is formatted to provide a date e.g. 17-Jun-09. Cell A80 is a numeric date
e.g. 39981. Is the VB code reading the formula in this case or is there a
problem with the formatting?

Thanks

 

[Jacob Skaria]

Kerry,

Try. The date value by default will have \ or / which is not an acceptable
chr..

ActiveSheet.Name = Format(Range("A81"),"dd-mmm-yyyy")

If this post helps click Yes

 

[Kerry]

Hi Jacob,
I thought of that so when I formatted cell A81 I made it DD-Month-YY but
that still didn't work. Your code has solved the problem.
Thankyou very much. There are so many tricks to VBA.

 

[Robert McCurdy]

Your code should resemble something like this Kerry.

Dim wks as worksheet
set wks = Sheets("Timesheet")

wks.PrintOut Copies:=1
wks.copy after:=wks
Sheets(wks.Index + 1).Name = wks.cells(81, 1).value

This avoids selecting or assuming the activesheet is really the one you want
the code to run on.
You might also consider if there is a valid sheet name in A81, and a cell 81
rows down is not a good place to have it, or see if it is correct. Sheet
name checking also requires no other sheet is named the same, so you can't
run this twice without an 1004 error.
Lastly if you are coping sheets, remember cells with more than 255
characters will be truncated.


Regards
Robert McCurdy

 

[Jacob Skaria]

and with multiple workbooks open

Dim wBook As Workbook
Dim wSheet As Worksheet
Set wBook = Workbooks("<Workbookname>")
Set wSheet = wBook.Sheets("Timesheet")

wSheet.Copy after:=wSheet
wBook.Sheets(wSheet.Index + 1).Name = wSheet.Cells(81, 1).Value
wSheet.PrintOut Copies:=1


If this post helps click Yes