Creating new worksheets in a workbook....with a twist

[londar]

I am currently trying to create somthing, I dont even know if excel i
capable of doing it however.

Basically I have a main page (Sheet 1) with a list in Column A wit
building sites. Currently it goes from A10-32. Now each of thes
currently has another worksheet within the workbook attached to it, an
the information in the according row is displayed in that workbook.

Is there a way in excel to create somthing so that when I add a nam
into cell A33 that it automatically creates a new Worksheet with
title of what is written in that cell? Only the Column A would be abl
to do this no other columns.



*Sorry for the dbl post....have NO idea which forum this would appl
to*

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"
Dim sh As Worksheet

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
On Error Resume Next
Set sh = Worksheets(.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name
= .Value
Me.Activate
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[londar]

thanks,

the '=.Value' area is giving me a compile error however?

 

[Bob Phillips]

wrap-around

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"
Dim sh As Worksheet

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
On Error Resume Next
Set sh = Worksheets(.Value)
On Error GoTo 0
If sh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count)) _
.Name = .Value
Me.Activate
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[londar]

it is still giving me an error right here:

Worksheets.Add(after:=Worksheets(Worksheets.Count)) _
..Name = .Value


I am correct in just copying and pasting what you typed out into the
code section correct.

I really appreciate the help

 

[Bob Phillips]

It works fine for me. Presumably, you are typing the value into column A?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[londar]

correct it is going into column A.

I copy and pasted exactly what you typed.

 

[Pete_UK]

Try splitting the line at a different point, i.e.:

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name _
= .Value

Hope this helps.

Pete

 

[Bob Phillips]

Did you copy and paste or type it? I believe Excel forum sometimes loses the
dots, so check the code here http://tinyurl.com/kckne

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[londar]

ya i have copied that and still nothing.
Just to make sure I am doing this right. I have the sheet where thes
locations I am adding located on SHeet1.

I am right clicking the Sheet1 tab and clicking view code. I am the
pasting that text as it appears into this area.

is this right or am I missing a big step causing these issues?

Thanks

 

[Bob Phillips]

yeah, that is what you should do

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)