Hello Sir,
in my last post i was asked to Mr. stojk89 sir to create / insert a new sheet automatically when i enter the name in Listed range
He explained me with beautiful solutions and with good example work book
the code is
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Dim n As Integer
Dim SheetExists As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo ResetApplication
Application.EnableEvents = False
Set isect = Intersect(Target, Range("B:B"))
If Not isect Is Nothing And Target <> "" Then
For n = 1 To Sheets.Count
If Sheets(n).Name = Target Then
SheetExists = True
Exit For
End If
Next
If Not SheetExists Then
ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Target
Me.Activate
Target.Select
Sheets("Sandhya Enterprises").Range("A:Z").Copy Sheets(Target.Value).Range("A1")
With Sheets(Target.Value)
.Range("B2").Value = .Name
End With
Sheets(Target.Value).Activate
End If
End If
ResetApplication:
Err.Clear
On Error GoTo 0
Application.EnableEvents = True
Set isect = Nothing
End Sub
Which base sheet is coping for new sheet, in the base sheet i insert the array formula
when i insert a new name in list then the sheet is copied and pasted in with inserted name
when i protect the base sheet then it will working but the formulas are not working it's coping as like text of the base sheet
so how can i copy the base sheet with formulas when the sheet is protected
if possible Kindly suggest
With Regards
Patnaik
Bookmarks