help

  • Thread starter Thread starter mc2
  • Start date Start date
M

mc2

i have a workbook with a sheet in wich a cell, with data validation, can
display a serie of names; i.e., example1, example2, example3, ....

what i want is this:

if i select in that cell, for example, example1, then if sheet example1
exists it will be slected, otherwise it will be created with that name.

is this clear? i hope so :)

i´ll apreciate any solution, and ..., thanks in advance.
 
Put the following code in the sheet module for the worksheet that
contains the cell with the validation:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Worksheets(Target.Value).Select
If Err.Number <> 0 Then
Worksheets.Add.Name = Target.Value
End If
End If
On Error GoTo 0
End Sub


Change $A$1 to the cell with the validation list.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
hi, thanks, it works great!

can you change the code to do the same but instead of adding a new sheet, it
will duplicate an existing one, and the duplicated will have the name that
is in the cell with the validation.
i hope i´m clear :)
 
Thanks Abdul Salam!

Dont work :)

i want duplicate one sheet, with all the things, ... formats ....



Modify code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Worksheets(Target.Value).Select
If Err.Number <> 0 Then
Sheet2.Select ' change to your name
Cells.Select
Selection.Copy
Worksheets.Add.Name = Target.Value
Selection.PasteSpecial Paste:=xlPasteAll
Range("A1").Select
End If
End If
On Error GoTo 0
End Sub


Abdul Salam
 
Back
Top