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 row 10 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

 

[Jim Jackson]

Sheets("Sheet1").activate
Range("A33").activate
x = Activecell
ActiveWorkbook.Worksheets.Add After:=ActiveSheet
Activesheet.name = x

 

[colofnature]

You could use the worsheet_change event like so:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.currentregion.rows.count > worksheets.count - 1 then
worksheets.add after:=worksheets(worksheets.count)
activesheet.name = target.value
end if
End Sub


Col

 

[Jim Jackson]

This example will select the next empty cell for the name to be inserted and
then add the sheet and name it.

Workbooks("Yourwbkname.xls").Activate
With Sheets("Sheet1")
Set rng = .Range(.Cells(1, 1), .Cells(1, 1)).End(xlDown)
rng.Select
ActiveCell.Offset(1, 0).Activate

If inputbox("Type the new name") = vbcancel then
end sub
else
end if
x = Activecell
ActiveWorkbook.Worksheets.Add After:=ActiveSheet
Activesheet.name = x
End With