paste named range for each new sheet created
Copy the following sub into ThisWorkbook module. It will insert the named
ranges in any worksheet that is activated; not just the added worksheets
(which are activaed by default when added). If you want to exclude any
existing worksheets then put an if statement at the start of the sub and exit
the sub if necessary.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error GoTo Create_Named_Ranges
Sh.Range("Error_Count").Select
Sh.Range("Error_Label").Select
Exit Sub
Create_Named_Ranges:
With ActiveWorkbook.Worksheets(Sh.Name).Names
.Add Name:="Error_Count", _
RefersToR1C1:=Sh.Range("A1")
.Add Name:="Error_Label", _
RefersToR1C1:=Sh.Range("B1")
End With
End Sub
--
Regards,
OssieMac
"J.W. Aldridge" wrote:
Exsisting sheet (Error Counter) has two named ranges.
Error_Count
Error_Label
How to automatically paste these two ranges to each sheet created in
workbook.
|