Using worksheet templates with named ranges

[Jeremy]

I have a fairly large workbook with worksheets that contain a lot of
formulas and named ranges that reference other worksheets in the
workbook. I'd like to be able to make templates based on two of the
worksheets, but when I copy them out of the workbook and try to use
them that way, I run into problems because copying them always results
in a link back to the workbook they came from, and besides that, the
named ranges seem to get converted to named ranges that are local to
the worksheet, which I don't want.

Aside from what I've been doing, which is taking the new template,
deleting all of its local named ranges and links, and then adding it to
the workbook, is there another way this can be handled? I'm running
into this problem increasingly often, and the method I'm using is
pretty slow and inefficient. Can anyone offer some advice?

 

[Alan]

Hi Jeremy,


Simple way, probably lacking code efficiency, but is a simple fix for me for
the same problem I was experiencing. Drops the info into the template.xls
and does a saveas over the top of the original, leaving the original
template.xls file in tact. I am a rookie, so this may not be what you are
looking for.

Dim Wb1 As Workbook
Set Wb1 = ActiveWorkbook
Dim Wb2 As Workbook
Set Wb2 = Workbooks.Open Filename:= "C:\Template.xls"
Wb1.Activate ' Activates original file.
Range("U1") = ActiveWorkbook.FullName
Dim wbName As String
wbName = Range("U1") ' Set the path and filename as a string
Range("So&So").Copy
Wb2.Activate
Range("So&So").Select

ActiveSheet.Paste ' Selection.Pastespecial xlvalues &or xlformats
' I use xlvalues to forgo bringing any unwanted formatting into the
' template since the template is preformatted.

Wb1.Close False

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs wbName 'Saves the template.xls as the original
path & filename
Application.DisplayAlerts = True