Create worksheet automatically

[daniel chen]

From any empty cell, I enter the company name,.
The following macro will validate the name with the index in column A of
worksheet named "Jounal"
This index lists all the worksheets bearing their names.
If valid, it will direct to that worksheet.
If it is not valid, I want to create one automatically bearing the name.
For example, if I enter Acer in any empty cell, run the macro and enter "y"
at prompt,
it will create Worksheet("Acer") and be selected.
Will someone help me to do that? Please.


Sub Macro1()
Dim reg As String
Dim regSht As String
Dim Ans As String
Dim NextRow As Integer

reg = ActiveCell.Value
If reg = "" Then Exit Sub
' I had manually created Worksheet("Jounal"),
'Worksheet("Apple"), Worksheet("Dell") and Worksheet("HP").
regSht = "Jounal"
Sheets(regSht).Select
Range("A1") = "Index:" ' as given
Range("A2") = "Jounal" ' as given
Range("A3") = "Apple" ' as given
Range("A4") = "Dell" ' as given
Range("A5") = "HP" ' as given
regSht = ""

If Not IsError(Application.Match(reg, Range("A2:A10"), 0)) Then
Sheets(reg).Select
GoTo proceeding1
Else
Ans = InputBox(prompt:="No such so" & vbLf & _
"Enter 'y' to create a new worksheet" & vbLf &
"Enter 'n' to abort")
If Ans = "y" Then GoTo Create_worksheet
If Ans = "n" Then Exit Sub
End If
Create_worksheet:
With Worksheets("Jounal")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
Cells(NextRow, 1) = reg
' I need help here to create a new worksheet. ?!?!?!?!?!?!?!

proceeding1:
End Sub

 

[Dave Peterson]

You could do it by validating a list on a separate worksheet, but then when you
add that new worksheet, you have to update that list.

And if someone (you???) changes a name, you may find a match for a worksheet
that isn't there--or not find a match when it it is there.

It might be better to just check to see if that sheet is really there.

And there is a worksheet event that you can tie into that monitors when
something gets changed on that sheet. You can limit it to only care about a
certain range.

If you like that idea, rightclick on the worksheet tab that should have this
behavior and select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim testWks As Worksheet
Dim resp As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Set testWks = Nothing
On Error Resume Next
Set testWks = Worksheets(Target.Value)
On Error GoTo 0

If testWks Is Nothing Then
resp = MsgBox(prompt:="wanna create a new sheet?", Buttons:=vbYesNo)
If resp = vbNo Then
'do nothing
Else
Set testWks = Worksheets.Add
On Error Resume Next
testWks.Name = Target.Value
If Err.Number <> 0 Then
'what should happen
MsgBox "Please rename " & testWks.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End If
End If

If testWks Is Nothing Then
'do nothing
Else
testWks.Select
End If

End Sub

I only looked for changes in column A with this line:

If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

You can modify to any range you want.

 

[daniel chen]

Hi, Dave
Here is another idea.
I will have a Worksheet("Index") that lists all the rest of the worksheets
within
the workbook in column A with an array of formulae that will track them.
(Is it possible to formulate such? )
Then a message "New worksheet is created" appears if it is so.
You are always helpful, thanks.

 

[Dave Peterson]

You can build a bunch of formulas that look like this:

=MID(CELL("filename",'Sheet1'!A1),FIND("]",CELL("filename",'Sheet1'!A1))+1,255)

Taken from the FAQ at Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName


But if you add sheets/delete sheets, you could still have trouble. (I'd look at
the existing sheets and just use what I found there--I don't see a reason to
keep track of an index.)

But here goes:

Option Explicit

Sub Macro1()
Dim reg As String
Dim regSht As String
Dim Ans As Long
Dim NextRow As Long

Dim gotoSheet As Worksheet

reg = ActiveCell.Value
If reg = "" Then Exit Sub

Set gotoSheet = Nothing

If IsError(Application.Match(reg, _
Worksheets("index").Range("A:A"), 0)) Then
Ans = MsgBox(prompt:="No sheet with that name" & vbLf & _
"Create a new sheet?", Buttons:=vbYesNo)
If Ans = vbNo Then
Exit Sub
End If
Set gotoSheet = Worksheets.Add
On Error Resume Next
gotoSheet.Name = reg
If Err.Number <> 0 Then
'what should happen
MsgBox "Please rename " & gotoSheet.Name & " manually"
Err.Clear
End If
With Worksheets("Index")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Formula _
= "=MID(CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "),FIND(""]"",CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "))+1,255)"
End With
Else
Set gotoSheet = Worksheets(reg)
End If

If gotoSheet Is Nothing Then
'do nothing
Else
gotoSheet.Select
End If
End Sub

And Index isn't your input sheet, right?

(and you'll have to run the macro (a button on this worksheet???) when you
want.)

 

[Dave Peterson]

Oops. wrong thread.

You can build a bunch of formulas that look like this:

=MID(CELL("filename",'Sheet1'!A1),FIND("]",CELL("filename",'Sheet1'!A1))+1,255)

Taken from the FAQ at Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName

But if you add sheets/delete sheets, you could still have trouble. (I'd look at
the existing sheets and just use what I found there--I don't see a reason to
keep track of an index.)

But here goes:

Option Explicit

Sub Macro1()
Dim reg As String
Dim regSht As String
Dim Ans As Long
Dim NextRow As Long

Dim gotoSheet As Worksheet

reg = ActiveCell.Value
If reg = "" Then Exit Sub

Set gotoSheet = Nothing

If IsError(Application.Match(reg, _
Worksheets("index").Range("A:A"), 0)) Then
Ans = MsgBox(prompt:="No sheet with that name" & vbLf & _
"Create a new sheet?", Buttons:=vbYesNo)
If Ans = vbNo Then
Exit Sub
End If
Set gotoSheet = Worksheets.Add
On Error Resume Next
gotoSheet.Name = reg
If Err.Number <> 0 Then
'what should happen
MsgBox "Please rename " & gotoSheet.Name & " manually"
Err.Clear
End If
With Worksheets("Index")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Formula _
= "=MID(CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "),FIND(""]"",CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "))+1,255)"
End With
Else
Set gotoSheet = Worksheets(reg)
End If

If gotoSheet Is Nothing Then
'do nothing
Else
gotoSheet.Select
End If
End Sub

And Index isn't your input sheet, right?

(and you'll have to run the macro (a button on this worksheet???) when you
want.)

Hi, Dave
Here is another idea.
I will have a Worksheet("Index") that lists all the rest of the worksheets
within
the workbook in column A with an array of formulae that will track them.
(Is it possible to formulate such? )
Then a message "New worksheet is created" appears if it is so.
You are always helpful, thanks.

 

[Dave Peterson]

You can build a bunch of formulas that look like this:

=MID(CELL("filename",'Sheet1'!A1),FIND("]",CELL("filename",'Sheet1'!A1))+1,255)

Taken from the FAQ at Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName


But if you add sheets/delete sheets, you could still have trouble. (I'd look at
the existing sheets and just use what I found there--I don't see a reason to
keep track of an index.)

But here goes:

Option Explicit

Sub Macro1()
Dim reg As String
Dim regSht As String
Dim Ans As Long
Dim NextRow As Long

Dim gotoSheet As Worksheet

reg = ActiveCell.Value
If reg = "" Then Exit Sub

Set gotoSheet = Nothing

If IsError(Application.Match(reg, _
Worksheets("index").Range("A:A"), 0)) Then
Ans = MsgBox(prompt:="No sheet with that name" & vbLf & _
"Create a new sheet?", Buttons:=vbYesNo)
If Ans = vbNo Then
Exit Sub
End If
Set gotoSheet = Worksheets.Add
On Error Resume Next
gotoSheet.Name = reg
If Err.Number <> 0 Then
'what should happen
MsgBox "Please rename " & gotoSheet.Name & " manually"
Err.Clear
End If
With Worksheets("Index")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Formula _
= "=MID(CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "),FIND(""]"",CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "))+1,255)"
End With
Else
Set gotoSheet = Worksheets(reg)
End If

If gotoSheet Is Nothing Then
'do nothing
Else
gotoSheet.Select
End If
End Sub

And Index isn't your input sheet, right?

(and you'll have to run the macro (a button on this worksheet???) when you
want.)

 

[Dave Peterson]

Hmmmm.

The indices in my newsreader were screwed up.

 

[daniel chen]

This might works.
I will figure a way to rename all the sheets as sht1,sht2,etc.
And then rename them sequencially as it goes along.
I will let you know. Thanks,

You can build a bunch of formulas that look like this:

=MID(CELL("filename",'Sheet1'!A1),FIND("]",CELL("filename",'Sheet1'!A1))+1,255)

Taken from the FAQ at Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName


But if you add sheets/delete sheets, you could still have trouble. (I'd
look at
the existing sheets and just use what I found there--I don't see a reason
to
keep track of an index.)

But here goes:

Option Explicit

Sub Macro1()
Dim reg As String
Dim regSht As String
Dim Ans As Long
Dim NextRow As Long

Dim gotoSheet As Worksheet

reg = ActiveCell.Value
If reg = "" Then Exit Sub

Set gotoSheet = Nothing

If IsError(Application.Match(reg, _
Worksheets("index").Range("A:A"), 0)) Then
Ans = MsgBox(prompt:="No sheet with that name" & vbLf & _
"Create a new sheet?", Buttons:=vbYesNo)
If Ans = vbNo Then
Exit Sub
End If
Set gotoSheet = Worksheets.Add
On Error Resume Next
gotoSheet.Name = reg
If Err.Number <> 0 Then
'what should happen
MsgBox "Please rename " & gotoSheet.Name & " manually"
Err.Clear
End If
With Worksheets("Index")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Formula _
= "=MID(CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "),FIND(""]"",CELL(""filename""," _
& gotoSheet.Range("a1").Address(external:=True) _
& "))+1,255)"
End With
Else
Set gotoSheet = Worksheets(reg)
End If

If gotoSheet Is Nothing Then
'do nothing
Else
gotoSheet.Select
End If
End Sub

And Index isn't your input sheet, right?

(and you'll have to run the macro (a button on this worksheet???) when you
want.)

Hi, Dave
Here is another idea.
I will have a Worksheet("Index") that lists all the rest of the
worksheets
within
the workbook in column A with an array of formulae that will track them.
(Is it possible to formulate such? )
Then a message "New worksheet is created" appears if it is so.
You are always helpful, thanks.