Automatically Rename Cells Closed

Question

Hi, i am using Excel 2010 ...quite poorly as my skills are basic.

I am trying to work out how to Incrementally number worksheets automatically  ie 20140001  2014002  2014003. 
I have read you need to use a macro and have seen some code ......but due to my basic level of knowledge need to know how and where to enter....
Anyone able to provide some detailed instructions so this monkey can understand?
Also, do i need to have open all the sheets before doing this macro or will it generate each time i open a new one?

Many thanks to anyone who can help

venkat1926 · Answer

open the file. do not worry whatever be the names of the worksheets

open vb editor (alt + F11)
click control+R
you will see all the open files. go to your file and highlight that file
a window will open
click insert in menu bar and click module in the sub menu
in the waindow that comes up 
copy the macro "test" given below
save the file.

now go to the spreadsheet
go to tools (in excel 2003 or before) 
click macro-macros
you see the macro "test" in the list and other mcros of open files 
highlight this macro "test"
click run on the right side

if your version is excel 2007 or later 
click  "developer" ribbon 
click "macros" (second  from left side)
you see the macro "test" and other mcros of open files 
highlight this macro and 
click run on the right side


the macro is 

Sub test()
Dim j As Integer, k As Integer
j = Worksheets.Count
Worksheets(1).Name = 2014001
For k = 2 To j
Worksheets(k).Name = Worksheets(k - 1).Name + 1
Next k

End Sub

Paul S · Answer

Many thanks....

I followed your detailed instructions (very helpful for a novice like me) and that worked well.

If I could trouble you or someone else further..... is there any way to;

1   Have a cell in Sheet 2014001 (say B3) derive a number the same as the worksheet name....not sure if this is possible as worksheet name might be alpha letters and I want cell B3 to be numeric ie worksheet 2014001  cell b3 2014001   worksheet 2014002 cell b3 2014002 worksheet 2014003 cell b3 2014003 

2   if not, no big problem as i can add the number manually

3   Have the contents of worksheet 2014001 to be copied automatically onto 2014002 when that sheet is opened. and onto 2014003 when that sheet is opened and so on  i am wanting the respective cell'sB3 to read 2014001, 2014002, 2014003 etc 

4   Make the numbering of sheets and copying of contents happen automatically every time a new worksheet is opened rather than asking the macro to run each time.

I am trying to develop an Excel File in which I can write Job instructions for my staff.  I have set up sheet 2014001 with a number of protected areas so that staff can't access, just leaving me room to write who is responsible for what and what the instructions are.

I am hoping to later link this Workbook to another Workbook that will record our Work in progress.....but that is a question for later....thanks for the help

venkat1926 · Answer

"Sheet 2014001 (say B3) derive a number the same as the worksheet "


if it is in only one street enter manually. if it is all the sheets sue a macro

by the by you can have number or text as name of the sheet.

sub fillb3

Sub fillb3()
Dim j As Integer, x
For j = 1 To Worksheets.Count

With Worksheets(j)
x = .Name

.Range("B3") = x
End With
Next j
End Sub



purpose of question 3 is not clear  however try this event code

open vbeditor
contorl+R
goto your file and right click "thisworkbook"
click viewcode
there copy this event code AND SAVE THE FILE


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "2014001" Then Exit Sub
ActiveSheet.Cells.Clear
Worksheets("2014001").Cells.Copy Sh.Range("A1")
End Sub
REMEMBER EVERY TIME YOU ACTIVATAE A SHEET THAT SHEET DATA IS LOST AND COPIED FROM THE FIRST SHEET. WILL THIS NOT GIVE PROBLEM FOR U
BE CAREFUL

Automatically Rename Cells

3 responses

Similar discussions

Subscribe To Our Newsletter!