Add multiple custom lists

[Chet]

I would like to add multiple custom lists taken from the
activeworkbook sheet1. Sheet1 would have n custom lists with each
list in a column starting in row 1. Each column will be a custom
list to be imported.

I'm not clear how to do this using the addcustomlist command. I have
some code but it's not working. (I think it's pretty far off from
working.)

sub AddMultipleLists()
NbrOfLists=4
For Index =1 to NbrOfLists
For i = Lbound(ListArray,1) to Ubound(ListArray,1)
Worksheets("sheet1").cells(i, ColIndex).Value =
ListArray(i)
application.addcustomlist Array(ListArray)
Next I
Next Index

Any ideas? Thx Chet

 

[Dave Peterson]

Maybe this saved post will help:

The first exports the lists to a new worksheet.

Then save the workbook and open it on a different pc.

The second imports those extracted lists into excel on the new pc.

Option Explicit
Sub ExtractCustomList()

Dim iCtr As Long
Dim myArray As Variant
Dim newWks As Worksheet

Set newWks = Worksheets.Add
newWks.Cells.NumberFormat = "@"

For iCtr = 5 To Application.CustomListCount
myArray = Application.GetCustomListContents(iCtr)
newWks.Cells(1, iCtr - 4) _
.Resize(UBound(myArray) - LBound(myArray) + 1).Value _
= Application.Transpose(myArray)
Next iCtr
End Sub
Sub ImportCustomList()

Dim iCol As Long
Dim wks As Worksheet
Dim myArray As Variant

Set wks = ActiveSheet

With wks
For iCol = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
myArray = .Range(.Cells(1, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp)).Value
Application.AddCustomList listArray:=myArray
Next iCol
End With
End Sub