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? |
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? |
All times are GMT +1. The time now is 08:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com