MS Excel Problem

  DANZIG 08:50 11 May 05
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 05

..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 05

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 05

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 05

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 05

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 05

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 05

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 05

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.

New Google phones UK release date | Pixel XL price, new features, specifications: Pixel X and…

1995-2015: How technology has changed the world in 20 years

This is the future of TV, VR and holograms

iPhone 7 review: Enhanced cameras, a refreshed design and water resistance make the iPhone 7 an…