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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

al_b_cnu

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

Try changing your IF statement:

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

HTH

Alan
 

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
 

kirbster

Board Regular
Joined
Feb 16, 2003
Messages
52

ADVERTISEMENT

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

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.
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,518

ADVERTISEMENT

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
 

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,101
Members
425,453
Latest member
bince

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