paste named range for each new sheet created

[J.W. Aldridge]

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.

 

[OssieMac]

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

 

[J.W. Aldridge]

Thanx, but I dont think I was quite clear...

I need the data (formulas & names & formats) within the original named
range on the existing sheet "Error Counter" to be pasted on each sheet
created.
Thanx

 

[J.W. Aldridge]

Thanx again, but found something that works!


Private Sub Workbook_newsheet(ByVal Sh As Object)

Sheets("ERROR COUNTER").Range("A1:I1").Copy Sh.Range("a1")

End Sub