| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Tab
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 -- Kerry |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
Couple more suggestions.
Worksheet name max 31 characters None of these characters : \ / ? * -- Regards, OssieMac "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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 -- Kerry "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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. -- Kerry "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Kerry" wrote: 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 -- Kerry |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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 "Kerry" wrote in message ... 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 -- Kerry |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Rename Tab
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 --------------- Jacob Skaria "Robert McCurdy" wrote: 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 "Kerry" wrote in message ... 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 -- Kerry |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rename | Excel Discussion (Misc queries) | |||
| how do i rename a sheet if the rename tab is inactive? | Excel Worksheet Functions | |||
| Rename a cell | Excel Discussion (Misc queries) | |||
| Rename Add-Ins | Excel Discussion (Misc queries) | |||
| Rename a sub | Excel Programming | |||
Linear Mode
