Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheet templates with named ranges
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using worksheet templates with named ranges
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 "Jeremy" wrote in message ups.com... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
relative Named Ranges based on worksheet | Excel Worksheet Functions | |||
trying to copy a worksheet containing named ranges to anotherworksheet | Excel Worksheet Functions | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
creating a VBA variabel from multiple named worksheet ranges | Excel Programming | |||
named ranges at workbook and worksheet levels | Excel Programming |