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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
hi,

Try changing your IF statement:

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

HTH

Alan
 
Upvote 0
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
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
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
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
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,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top