Custom Lists-Transfer

D

Dave Peterson

I don't know.

But you can use a macro to retrieve the custom list and another macro to import
it.

Saved from a previous post.

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


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Top