How to generate multiple worksheets based on data

[jb]

I have a table of 10 rows and one column.

This rows will be filled by user and it is not fixed that how many rows user will fill.

But it is sure that user will not fill more than 10 rows. Data will be unique.

Now for eg. used has filled 5 rows with following data:

ABC

XYZ

PQR

LMN

DEF

Now I have one button "Generate Sheet". I want to generate sheets with name ABC, XYZ, PQR, LMN and DEF for this example when the button is pressed. What will be the coding to perform this task?

 

[vasim]

Would suggest to post some of your code and find help from experts....I have done the same...


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.

 

[vasim]

http://www.ozgrid.com/forum/showthread.php?t=90099


This might help

 

[jb]

I got the following code and tried. But it is not working properly. It is changing name of active sheet and displays message Invalid name. Please help me to correct the code.


Sub AddSheets()

Dim cell As Excel.Range

For Each cell In Sheets(1).Range("A2:A11")


With cell


If Len(.Value) > 0 Then


On Error Resume Next


Worksheets(.Value).Select


If Err Then


Err.Clear


Worksheets.Add After:=Worksheets.Count


ActiveSheet.Name = .Value


If Err Then MsgBox "Invalid name!", vbCritical


On Error Goto 0


Else


On Error Goto 0


MsgBox "Sheet already exists", vbInformation


End If


End If


End With


Next cell

End Sub

 

[shrivallabha]

Try this way:

[pre]

Private Sub Generate_Click()
Dim r As Range
For Each r In Range("A2:A11")
If Len(Trim(r.Value)) <> 0 Then
If bSheetExists(Trim(r.Value)) Then
MsgBox "Sheet with name : " & Trim(r.Value) & " already exists!", vbExclamation
Else
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Trim(r.Value)
End If
End If
Next r
End Sub
Public Function bSheetExists(strName As String) As Boolean
On Error Resume Next
If Sheets(strName).Name = "" Then
bSheetExists = False
Else
bSheetExists = True
End If
On Error GoTo 0
End Function

[/pre]

 

[jb]

It worked. Thanks. But I have done so many trial and error for doing this task. So can anybody tell me the correct method to associate vba code with form control?

 

[shrivallabha]

Glad to know that it worked for you.


But I do not understand your question.


Do you mean you want to connect the macro to a form control button?

 

[jb]

Yes. I want to know the correct procedure. I am in learning phase.

So I forgot that what actually I have done.

Please help me.