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)

wbkSrcBook.Close SaveChanges:=False


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

MsgBox "No files selected", Title:="Merge Excel files"
End If

End Sub

1 Reply


Broadly speaking you need a further loop for each file to run through its Name Collection.

For Each definedName In fnameCurFile.Names



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.