Macro - New sheet renaming problem

kirbster

Board Regular
Joined
Feb 16, 2003
Messages
52
Can I pick your brains on a macro issue I have.... this little thing here looksup a value in a table in sheet "tlm", creates a new sheet and then changes the name of the sheet to the cell value. All well and good, except it renames "tlm" with the first value it finds...

Sheets("tlm").Select
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Cell.Value Like "*Count*" Then Worksheets.Add
ActiveSheet.Name = Cell.Value
Next Cell
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
hi,

Try changing your IF statement:

If Cell.Value Like "*Count*" Then
Worksheets.Add
ActiveSheet.Name = Cell.Value
End If

HTH

Alan
 
Upvote 0

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

Code:
Sub xxx()
For Each Cell In Sheets("tlm").Range("A2", Range("A2").End(xlDown))
If Cell.Value Like "*Count*" Then Worksheets.Add
ActiveSheet.Name = Cell.Value
Next Cell
End Sub
 
Upvote 0

kirbster

Board Regular
Joined
Feb 16, 2003
Messages
52
Thanks for that, however it just seems to change the name of the "tlm" sheet to the last cell in the list...I was hoping it would create new sheets and rename them to all cell values within that column..

Sorry...
 
Upvote 0

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
I don't know, it works for me, however

Code:
Sub xxx()
For Each Cell In Sheets("tlm").Range("A2", Range("A2").End(xlDown))
If Cell.Value Like "*Count*" Then
Worksheets.Add
ActiveSheet.Name = Cell.Value
End If
Next Cell
End Sub

Is better if there maybe are no matching cells.
 
Upvote 0

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
Hi,

Sorry, what I posted before was so off the mark :oops: .

try this code:

Code:
Dim Sht As Worksheet
Dim Cell As Range
Dim Rng As Range
Dim lEndRow As Long
Dim sName As String

Set Sht = Sheets("Sheet2")
lEndRow = Sht.Range("A2").End(xlDown).Row
Set Rng = Sht.Range("A2:A" & lEndRow)
For Each Cell In Rng
If Cell.Value Like "*Count*" Then
    MsgBox Sheets.Count
    Sheets.Add after:=Sheets(Sheets.Count)
    MsgBox Sheets.Count
    sName = Sheets(Sheets.Count).Name
    Sheets(Sheets.Count).Name = Cell.Value
End If

Next Cell

HTH

Alan
 
Upvote 0

kirbster

Board Regular
Joined
Feb 16, 2003
Messages
52
I nailed it...nailed it with

Sub xxx()

Sheets("tlm").Select
For Each cell In Sheets("tlm").Range("A2", Range("A2").End(xlDown))
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = cell.Value

Next cell


End Sub
 
Upvote 0

Forum statistics

Threads
1,186,911
Messages
5,960,543
Members
438,483
Latest member
Shahin Jack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top