Create Sheet Tabs from the List (URGENT)

  • Thread starter Thread starter Murtaza
  • Start date Start date
M

Murtaza

I have to create worksheets of 1000 customer in a single workbook, I have
listed the customers name in a seperate word file. Can I programatically
create sheets from that list of customers.

Any help will be highly appreciated in this regard.

Murtaza
 
Hi Murtaza,

modified from code on my sheets.htm page
will create form a selection that you might have pasted into
Excel from MS Word. The sheets will be added to
the end and cells that are not text constants will be rejected.
i.e. numbers will be rejected.

For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Sheets.Add After:=Sheets(Sheets.Count)

Directions to install the macro below can be found in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Additional reading:
http://www.mvps.org/dmcritchie/excel/proper.htm
http://www.mvps.org/dmcritchie/excel/sheets.htm

suggest you start with a new workbook for testing purposes.

-------------------------------------------------
Sub genWStabnames2()
'David McRitchie based on previous code in sheets.htm
Dim cell As range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Value
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failure to Rename Worksheet:")
'--eliminate already created sheet that was to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediae cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub
 
Hi Murtaza;

Firstly create a sheet named "Names" copy the names from that word file to
sheet"names".
The names must start from cell A1, then A2,A3....
Now run this macro and new sheets will be added to your file with the names
on the list.

Sub AddNewSheetsFromTheList()
Cells(65536, 1).Select
Selection.End(xlUp).Select
last = ActiveCell.Row
For i = 1 To last
a = Sheets("Names").Cells(i, 1)
Set NewSheet = Worksheets.Add
NewSheet.Name = a
Next
End Sub

* I did not test this macro with 1000 names.I am not sure this will work
fine with a list bigger than 254 names on list. Because it is not safe to
use more sheets than 255 sheets on one xls file.You can create more sheets
but the safety of your data will be far away from your file. Even you may
not be able to open this file after adding some data and formulae.(Only a
warning)

Burçin SARIHAN
 
Thanks David, it really works find, but i also want to rename those sheets
with a value in cell
For Example:
A1
R1 Murtaza
R2 David
R3 McRitchie

So after code, I should have 4 Sheets (Sheet1 contain List of Names,
Sheet2=Murtaza, Sheet3=David, Sheet4=McRitchie)

Any idea how can i do that?
 
Thanks BS, your macro works fine.

I have a sample format sheet, and I want to copy that sheet instead of
Default Blank sheets. Your macro create blank sheets, but i want to create
named sheets with my format.

Hope you understand & must have some solutions.

Regards,
Murtaza
 
Hi Murtaza,
I get the results you asked for. I expect that you ran a different macro
than what I provided. The sheetnames I generate are at the END of
your worksheet names, so I suggest you test from a sheet near the
end.

Any failure to rename a sheetname
will eliminate that sheet so you cannot be creating Sheet1, Sheet2, etc
unless that was what you had in your selection, or you modified the code.

I made a couple of changes to the macro, including use of .text instead of
..value which would not affect you. Try recopying the macro and
retesting.

Sub GenWStabnames2()
'David McRitchie based on previous code in sheets.htm
Dim cell As range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub

I get the results you asked for, as long as the cells are constants
(not formulas), are not numeric, and as long as they don't contain characters
that are invalid in a sheetname the sheets will be generated with
each of the names from your selected area. Dates are numbers,
and a cell with 123 even if formatted as text is a number and will
be ignored in the selection. None of that should affect what you
provided as an example.
 
Hi David

thanks for your efforts, your macro create default blank sheets, but i want
to create sheet based on my sample sheet. I have tried XLStart folder
options but it not work.

any comments?

Regards,
Murtaza
 
Hi Murtaza,
Please try to ask the entire question in the initial question.
Oh and don't put URGENT on the subject title, everybody's question
is important to them. Some people will avoid such questions, while
others may purposely select them first, it really won't improve the
quality or the speed in getting an answer.

It is disconcerting to be told that what I wrote does not work,
not having seen the other question you asked in this thread in reply to
someone else. when in fact you are asking an additional question
to each person that answers.

This is not a very popular newsgroup it was dropped for several
years by Microsoft. Essentially excel.misc would be the
group for general questions, and excel.worksheet.functions
for builtin worksheet functions, but since yours is a programming
question and you know how to install and use a macro the
newsgroup for future questions (new threads) concerning VBA
questions in Excel would be excel.programming

Sub GenWStabnames2()
'David McRitchie based on previous code in sheets.htm
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
'Sheets.Add after:=Sheets(Sheets.Count)

Worksheets("MyBeginning").Copy after:=Worksheets(Worksheets.Count)

If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub

--


Murtaza said:
Hi David

thanks for your efforts, your macro create default blank sheets, but i want
to create sheet based on my sample sheet. I have tried XLStart folder
options but it not work.

any comments?

Regards,
Murtaza
Click to expand...
---original question---
 
Dear David,

Thanks for your advise (although a little rude, but I don't mind) for
improving my newsgroup-etiquettes. In fact I am the newbie for the
NewsGroups, It was my 1st or 2nd message to any newsgroup. Secondly, I have
not actually figured-out everything so whenever any problem arise I will
refer to newsgroup's MVPs.


Anyways, thanks for your efforts.

Murtaza
 
Hi Murtaza,

The main question is does the code work for you or not. Your
"format" sheet as you called it would be "MyBeginning" in the
code I gave you.

Worksheets("MyBeginning").Copy after:=Worksheets(Worksheets.Count)

The complete macro creates sheetnames like you asked for, those
worksheets are copies of another worksheet as you asked in your reply to
Burçin SARIHAN, and in your reply to me after the one you told me my
code did not work without giving a reason.

Feedback, is what newsgroups are about. Trying to ask a question
or to give an answer without a lot of back and forth conversation
to clarify things is a goal.

You must have read or heard something about netiquette before you
posted; otherwise, you would not know the term. The following may
provide some help in working with the Excel newsgroups. Basically
they are the same as for other groups but there are variations. Here
is ours
Hints for New Posters (Chip Pearson's webpage)
http://www.cpearson.com/excel/newposte.htm

and explanations to some of the above, and more information
Posting to Newsgroups
http://www.mvps.org/dmcritchie/excel/posting.htm

Unfortunately such lists usually will appear very negative because
they generally are a lot of "don't"s. There was one that I knew of
that had no negatives until it got hacked up by a group effort after which
the "don't"s are even highlighted (Desk Top Systems).

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Thanks David,

Your latest code worked, I had replace the "MyBegining" sheet with my
"FormatedSheet". It really saves my time & efforts.

Murtaza
David McRitchie said:
Hi Murtaza,

The main question is does the code work for you or not. Your
"format" sheet as you called it would be "MyBeginning" in the
code I gave you.

Worksheets("MyBeginning").Copy after:=Worksheets(Worksheets.Count)

The complete macro creates sheetnames like you asked for, those
worksheets are copies of another worksheet as you asked in your reply to
Burçin SARIHAN, and in your reply to me after the one you told me my
code did not work without giving a reason.

Feedback, is what newsgroups are about. Trying to ask a question
or to give an answer without a lot of back and forth conversation
to clarify things is a goal.

You must have read or heard something about netiquette before you
posted; otherwise, you would not know the term. The following may
provide some help in working with the Excel newsgroups. Basically
they are the same as for other groups but there are variations. Here
is ours
Hints for New Posters (Chip Pearson's webpage)
http://www.cpearson.com/excel/newposte.htm

and explanations to some of the above, and more information
Posting to Newsgroups
http://www.mvps.org/dmcritchie/excel/posting.htm

Unfortunately such lists usually will appear very negative because
they generally are a lot of "don't"s. There was one that I knew of
that had no negatives until it got hacked up by a group effort after which
the "don't"s are even highlighted (Desk Top Systems).

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Murtaza said:
Thanks for your advise (although a little rude, but I don't mind) for
Click to expand...
Click to expand...
 
Back
Top