Forum Discussion
Rudrabhadra
Oct 31, 2022Brass Contributor
Named range delete VBA
While trying to combine different workbooks in a folder to worksheets in a workbook. (which I have VBA code). while running the code a message is displayed that "The name "xxxxx" already exists Click yes to use that Version of the name............" and I have to click on yes to all in all worksheets when added.
I wish to delete the named ranges in all the workbooks before combining it in one workbook.
How could I edit the code for the same. The code using to combine the worksheet is below.
Sub Cblschcomb()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
Dim Sh As Worksheet
Dim Um As Range
Dim db As Range
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean <> VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next
wbkSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If
Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub
- PeterBartholomew1Silver Contributor
Broadly speaking you need a further loop for each file to run through its Name Collection.
For Each definedName In fnameCurFile.Names
definedName.Delete
Bear in mind that this could destroy every formula in your new workbook (assuming there are formulas and that they use the defined Names). To bring Names across coherently, they all need to be scoped to the Worksheet rather than using global Workbook-scoped Names.