Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying a Workbook's Names to Another Workbook
I am trying to write a macro that gets a given workbooks' names and
applies them another workbooks links, which currently use letter/number notation, to that master workbook. This is because Excel's own built-in Apply Names functionality does not span workbooks as near as I can tell. Below is my attempt at a macro towards this end. It dies on the "Cells.ApplyNames" line with a 1004 error. Can anyone provide some advice? Regards, Matthew Sub ApplyNamesFromMasterSheet() Dim intNamesIdx As Integer Dim oName As Name Dim i As Long Dim wkbk As Workbook Dim sh As Worksheet Dim aryNames As Names Set aryNames = ActiveWorkbook.Names Debug.Print aryNames.Count With Application.FileSearch .NewSearch .LookIn = "\\my_server_name\my_directory\" '.LookIn = ThisWorkbook.Path .SearchSubFolders = False .Filename = "my_workbook.xls" .MatchTextExactly = True .FileType = msoFileTypeExcelWorkbooks If .Execute() 0 Then Debug.Print "There were " & .FoundFiles.Count & _ " file(s) found. Please wait while updating." Application.ScreenUpdating = False For i = 1 To .FoundFiles.Count If .FoundFiles(i) < ThisWorkbook.FullName Then Workbooks.Open Filename:=.FoundFiles(i), UpdateLinks:=0 Set wkbk = ActiveWorkbook Debug.Print wkbk.FullName For Each sh In wkbk.Worksheets Debug.Print sh.Name sh.Cells.ApplyNames Names:=aryNames Next 'wkbk.Save 'wkbk.Close SaveChanges:=False End If Next i Debug.Print "Update finished." Application.ScreenUpdating = True Else Debug.Print "There were no files found." End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying users to site/names | Excel Worksheet Functions | |||
Applying range names to existing formulas in separate worksheets | Excel Discussion (Misc queries) | |||
Applying same macro to all worksheets in workbook | Excel Discussion (Misc queries) | |||
Applying names to ranges of cells for formulas | Excel Programming | |||
Function to List an Excel Workbook's Sheet Names | Excel Discussion (Misc queries) |