Automatically Rename Cells

Closed
Paul S Posts 3 Registration date Sunday July 7, 2013 Status Member Last seen July 17, 2013 - Jul 7, 2013 at 06:51 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 18, 2013 at 12:34 AM
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

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 11, 2013 at 07:25 AM
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
0
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 18, 2013 at 12:34 AM
"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
0