VBA Help naming sheets

[jlclyde]

I am having trouble naming sheets based on a range of cells. I want a
new worksheet to be added and be named after new data in range
B7:B25. I will insert a button or somethign to run the macro, I just
need some help with the code to do it. The range is a list of names.
After I have entered names, I want to push a button and have the new
names be their own tabs.

Please help,
Jay

 

[Guest]

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht

End Sub

 

[jlclyde]

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht

End Sub






- Show quoted text -

How do you get it to stop after the last entry and not add another
blank sheet?
Jay

 

[Guest]

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht

End Sub

 

[jlclyde]

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht

End Sub






- Show quoted text -

Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay

 

[Guest]

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.

I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.

 

[jlclyde]

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.

I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.

Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay- Hide quoted text -

- Show quoted text -

Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay

 

[jlclyde]

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.

I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.

- Show quoted text -

Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht


End Sub

This is what I changed teh code to. I thought that i would take out
any of the blank cells out of the equation.
jay

 

[Guest]

Does the sheet name already exist? Do we ned to test if the namealready
exists?

If you highlight "sht" and right click you can add it to the watch window
and see the value of sht.

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.

I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.

:
Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht

End Sub

:
Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht

End Sub

:
I am having trouble naming sheets based on a range of cells. I want a
new worksheet to be added and be named after new data in range
B7:B25. I will insert a button or somethign to run the macro, I just
need some help with the code to do it. The range is a list of names.
After I have entered names, I want to push a button and have the new
names be their own tabs.

Please help,
Jay- Hide quoted text -

- Show quoted text -

How do you get it to stop after the last entry and not add another
blank sheet?
Jay- Hide quoted text -

- Show quoted text -

Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay- Hide quoted text -

- Show quoted text -

Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht


End Sub

This is what I changed teh code to. I thought that i would take out
any of the blank cells out of the equation.
jay

 

[jlclyde]

Does the sheet name already exist? Do we ned to test if the namealready
exists?

If you highlight "sht" and right click you can add it to the watch window
and see the value of sht.

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.
I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.
:
Sub addsheets()
Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht
End Sub
:
Sub addsheets()
Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht
End Sub
:
I am having trouble naming sheets based on a range of cells. I want a
new worksheet to be added and be named after new data in range
B7:B25. I will insert a button or somethign to run the macro, I just
need some help with the code to do it. The range is a list of names.
After I have entered names, I want to push a button and have the new
names be their own tabs.
Please help,
Jay- Hide quoted text -
- Show quoted text -
How do you get it to stop after the last entry and not add another
blank sheet?
Jay- Hide quoted text -
- Show quoted text -
Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay- Hide quoted text -
- Show quoted text -
Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay- Hide quoted text -
- Show quoted text -

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht

End Sub

This is what I changed teh code to. I thought that i would take out
any of the blank cells out of the equation.
jay- Hide quoted text -

- Show quoted text -

Here is what I have in B starting at B7
Dog
Cat
Monkey
Dog

When I run this macro

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht

End Sub

It adds all the right sheets and then one more. plus the other stuff
that i said. See fi maybe you can recreate my problem and tell me how
to fix it. I know that your code is close.

Thanks,
Jay

 

[Guest]

You have dog in your list twice! I already had the fix for the code. After
your first posting I found a similar posting and modified the code I sent
you. Here is the fx to test if the sheet already exists.

Look at his posting
http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each cell In sheetnames
If cell <> "" Then

Found = False
For Each sht In ThisWorkbook.Sheets
If sht.Name = cell Then
Found = True
Exit For
End If
Next sht
If Found = False Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell
End If
End If
Next cell

End Sub

Does the sheet name already exist? Do we ned to test if the namealready
exists?

If you highlight "sht" and right click you can add it to the watch window
and see the value of sht.

On Nov 20, 12:57 pm, Joel <[email protected]> wrote:

The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.

I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.

:
Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht

End Sub

:
Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht

End Sub

:
I am having trouble naming sheets based on a range of cells. I want a
new worksheet to be added and be named after new data in range
B7:B25. I will insert a button or somethign to run the macro, I just
need some help with the code to do it. The range is a list of names.
After I have entered names, I want to push a button and have the new
names be their own tabs.

Please help,
Jay- Hide quoted text -

- Show quoted text -

How do you get it to stop after the last entry and not add another
blank sheet?
Jay- Hide quoted text -

- Show quoted text -

Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay- Hide quoted text -

- Show quoted text -

Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay- Hide quoted text -

- Show quoted text -

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht

End Sub

This is what I changed teh code to. I thought that i would take out
any of the blank cells out of the equation.
jay- Hide quoted text -

- Show quoted text -

Here is what I have in B starting at B7
Dog
Cat
Monkey
Dog

When I run this macro

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht

End Sub

It adds all the right sheets and then one more. plus the other stuff
that i said. See fi maybe you can recreate my problem and tell me how
to fix it. I know that your code is close.

Thanks,
Jay

 

[jlclyde]

You have dog in your list twice! I already had the fix for the code. After
your first posting I found a similar posting and modified the code I sent
you. Here is the fx to test if the sheet already exists.

Look at his posting:http://www.microsoft.com/office/community/en-us/default.mspx?dg=micro...

Sub addsheets()

Set sheetnames = Range("B7:B25")
For Each cell In sheetnames
If cell <> "" Then

Found = False
For Each sht In ThisWorkbook.Sheets
If sht.Name = cell Then
Found = True
Exit For
End If
Next sht
If Found = False Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = cell
End If
End If
Next cell

End Sub

Does the sheet name already exist? Do we ned to test if the namealready
exists?
If you highlight "sht" and right click you can add it to the watch window
and see the value of sht.
:
The code doesn't run an extra loop. It only runs through the range of cells
specified. There is some data in the last cell that it ran that cannot be
used in a sheet name. Sheet names cannot contain the following six
characters / \ ? * [ ]. The sheet name cannot be larger than 31 characters.
I can add some additional tests for bad sheetname but can you tell me which
names it doesn't like and how you want the problem fixed.
:
Sub addsheets()
Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
if sht <> "" then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
end
Next sht
End Sub
:
Sub addsheets()
Set sheetnames = Range("B7:B25")
For Each sht In sheetnames
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Next sht
End Sub
:
I am having trouble naming sheets based on a range of cells. I want a
new worksheet to be added and be named after new data in range
B7:B25. I will insert a button or somethign to run the macro, I just
need some help with the code to do it. The range is a list of names.
After I have entered names, I want to push a button and have the new
names be their own tabs.
Please help,
Jay- Hide quoted text -
- Show quoted text -
How do you get it to stop after the last entry and not add another
blank sheet?
Jay- Hide quoted text -
- Show quoted text -
Even when you add the if after End it still is not working properly.
It adds one extra sheet and then throws an error.
Jay- Hide quoted text -
- Show quoted text -
Joel
ActiveSheet.Name = sht (this is highlighted after I try to debug it.
It adds an extra sheet. For example if I have five names and it
currently has 2 sheets, it will add the 5 names, it will add a 6 sheet
named Sheet3 and then it will throw the error. There is nothing in
any of the other cells. I have cleared coments and deleted them
twice. Let me kow if you can think of anything to fix this.
Thanks,
Jay- Hide quoted text -
- Show quoted text -
Sub addsheets()
Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht
End Sub
This is what I changed teh code to. I thought that i would take out
any of the blank cells out of the equation.
jay- Hide quoted text -
- Show quoted text -

Here is what I have in B starting at B7
Dog
Cat
Monkey
Dog

When I run this macro

Sub addsheets()

Set sheetnames = Range("B7", Sheet1.Range("B25").End(xlUp))
For Each sht In sheetnames
If sht <> "" Then
Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = sht
Else: Exit Sub
End If
Next sht

End Sub

It adds all the right sheets and then one more. plus the other stuff
that i said. See fi maybe you can recreate my problem and tell me how
to fix it. I know that your code is close.

Thanks,
Jay- Hide quoted text -

- Show quoted text -

Joel i am not usually that stupid..... Thank you for looking at it so
many times today. These are not the actual names that I am goign to
use and were just placeholders.

Sorry,
Jay