List names in a certain workbook into sheet.
And Deletes those names.
You may go through name after listing them and decide what to keep what to delete.
Sub List_of_Names(Optional inWb1 = "This", Optional SaveTo_Wb = "This", Optional SaveTo_Sh = "Sheet1", Optional SaveTo_Cell = "D4")
' List all names in this workbook and where do they refer to in sheet1
' Needs 3 columns starting from SaveTo_Cell to save ID | Name | ReferTo
If inWb1 = "This" Then inWb1 = ThisWorkbook.Name
If inWb1 = "Active" Then inWb1 = ActiveWorkbook.Name
If SaveTo_Wb = "This" Then SaveTo_Wb = ThisWorkbook.Name
If SaveTo_Wb = "Active" Then SaveTo_Wb = ActiveWorkbook.Name
X1=1
For Each NNa in Workbooks(inWb1).Names
X1 = X1 + 1
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 0).Value = X1
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 1).Value = NNa.Name
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 2).Value = NNa.RefersToR1C1
DoEvents
Next
End Sub
Sub DeleteNames(Optional inWb1 = "This", Optional ListFrom_Wb = "This", Optional ListFrom_Sh = "Sheet1", Optional ListFrom_Cell = "D4")
' Delete all names found in a list
If inWb1 = "This" Then inWb1 = ThisWorkbook.Name
If inWb1 = "Active" Then inWb1 = ActiveWorkbook.Name
If ListFrom_Wb = "This" Then ListFrom_Wb = ThisWorkbook.Name
If ListFrom_Wb = "Active" Then ListFrom_Wb = ActiveWorkbook.Name
X1 = 1
Do
NextName = Workbooks(ListFrom_Wb).Worksheets(ListFrom_Sh).Range(ListFrom_Cell).Offset(X1, 1).Value
If NextName = "" Then Exit Do
For Each NNa in Wb1.Names
If UCase(NN1.Name) = UCase(NextName) Then
NN1.Delete
Exit For
End If
DoEvents
Next
DoEvents
X1=X1+1
Loop
End sub
' List all names in this workbook and where do they refer to in sheet1
' Needs 3 columns starting from SaveTo_Cell to save ID | Name | ReferTo
If inWb1 = "This" Then inWb1 = ThisWorkbook.Name
If inWb1 = "Active" Then inWb1 = ActiveWorkbook.Name
If SaveTo_Wb = "This" Then SaveTo_Wb = ThisWorkbook.Name
If SaveTo_Wb = "Active" Then SaveTo_Wb = ActiveWorkbook.Name
X1=1
For Each NNa in Workbooks(inWb1).Names
X1 = X1 + 1
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 0).Value = X1
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 1).Value = NNa.Name
Workbooks(SaveTo_Wb).Worksheets(SaveTo_Sh).Range(SaveTo_Cell).Offset(X1, 2).Value = NNa.RefersToR1C1
DoEvents
Next
End Sub
Sub DeleteNames(Optional inWb1 = "This", Optional ListFrom_Wb = "This", Optional ListFrom_Sh = "Sheet1", Optional ListFrom_Cell = "D4")
' Delete all names found in a list
If inWb1 = "This" Then inWb1 = ThisWorkbook.Name
If inWb1 = "Active" Then inWb1 = ActiveWorkbook.Name
If ListFrom_Wb = "This" Then ListFrom_Wb = ThisWorkbook.Name
If ListFrom_Wb = "Active" Then ListFrom_Wb = ActiveWorkbook.Name
X1 = 1
Do
NextName = Workbooks(ListFrom_Wb).Worksheets(ListFrom_Sh).Range(ListFrom_Cell).Offset(X1, 1).Value
If NextName = "" Then Exit Do
For Each NNa in Wb1.Names
If UCase(NN1.Name) = UCase(NextName) Then
NN1.Delete
Exit For
End If
DoEvents
Next
DoEvents
X1=X1+1
Loop
End sub
Optional inWb1 = "This", Optional SaveTo_Wb = "This", Optional SaveTo_Sh = "Sheet1", Optional SaveTo_Cell = "D4"
and
Optional inWb1 = "This", Optional ListFrom_Wb = "This", Optional ListFrom_Sh = "Sheet1", Optional ListFrom_Cell = "D4"
and
Optional inWb1 = "This", Optional ListFrom_Wb = "This", Optional ListFrom_Sh = "Sheet1", Optional ListFrom_Cell = "D4"
Views 115
Downloads 46
CodeID
DB ID