MS Excel Problem

  DANZIG 08:50 11 May 2005
Locked

I have developed a workbook on excel at work.

There is one thing that I would like it to do that I can't at the minute though.

Its a bit tough to explain but I will do my best.

Information entered on worksheet 1 is automatically transferred on to other worksheets in the workbook. This works fine.

What I would like to do however is automatically change the name of the other worksheet, on the little tab thing at the bottom of the other worksheet, to information entered into worksheet 1.

Hope that makes sense.

  pauldonovan 08:58 11 May 2005

..using a macro..what would the 'trigger' be for changing the worksheet title?

i.e. is it when a particular cell changes?

  DANZIG 09:04 11 May 2005

Yes.

The idea is when information is entered into a certain cell on the first worksheet it will automatically change the name on the tab of the other worksheet it pertains to.

Thats what I want it to do..but I have no idea how.

  pauldonovan 10:43 11 May 2005

Say the sheets get named depending on the value of A1 on Sheet1.

Go to Sheet1.

Press Alt + F11 to bring up the VBA Editor. Double-click on Sheet1 under the "Microsoft Excel Objects" on the left.

Copy and paste the following code into the window on the right hand side:

' Code Starts Here
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Worksheets(2).Name = Range("A1").Value
End If


End Sub
' Code Ends Here

What that is saying is - whenever the worksheet changes (unless by calculation - if you want a calculated cell to determine the title we need something different), then update worksheet 2's name to be the value in Cell A1 on Worksheet1.

See what I mean?

I can email you an example or let me know if you need further help. I've assumed you haven't used VBA before in the above, apologies if you know some of this.

Please let me know if it helps.

  pickle factory 10:55 11 May 2005

Insert this code as a sub for sheet1, action it with a command button or set up function key and hey presto


Sub RenameSheets()
Dim Rng As Range, c As Range, i As Integer

Set Rng = [a1:a3] 'replace this with your range of names
For Each c In Rng
i = i + 1
On Error Resume Next
Sheets(i).Name = c
Next c

End Sub

  pickle factory 10:58 11 May 2005

Sub RenameSheets()
Dim Rng As Range, c As Range, i As Integer

Set Rng = [a1:a3] 'replace this with your range of names
For Each c In Rng
i = i + 1
On Error Resume Next
Sheets(i).Name = c
Next c

End Sub

  DANZIG 10:59 11 May 2005

Wow!!!

Sounds complex!!

I will give it a shot though when I get back to work and get back to you with the results.

Thankyou...

(Should earn me Brownie points if I can get it working)

  pickle factory 11:07 11 May 2005

Sub RenameSheets()
Dim Rng As Range, c As Range, i As Integer
Set Rng = [a1:a3] 'replace this with your range of names
For Each c In Rng
i = i + 1
On Error Resume Next
Sheets(i).Name = c Next c

End Sub

  pickle factory 11:10 11 May 2005

How the hell do I get this to hold the line breaks? It all comes out as one line but should be 10 lines in total.

You need a double line break in.

HTH

Sub RenameSheets()

Dim Rng As Range, c As Range, i As Integer

Set Rng = [a1:a3] 'replace this with your range of names

For Each c In Rng

i = i + 1

On Error Resume Next

Sheets(i).Name = c

Next c

End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?