Macro turning colums w/headers into list on multiple sheets

[Guest]

Hi All,
I'm trying to use a macro to create lists out of the columns in multiple
worksheets. The worksheets will have specific names but they could vary as
well as the column header names. Example:

Sheet 1 (Named Roster by Building)

Columns Bldg# Room# Occupant
123 321 Smith, Joe
123 432 Doe, Jane
Sheet 2 would have a different name and column headings but I want it to do
the same thing; create lists from all the columns so I can sort from any one
of the rows or multiple rows as needed. I want it to do this for all the
sheets in the file at one time. Also I don't need the data moved to another
sheet, I want it to create the lists just as if I had selected the columns
and gone to Data>List>Create List. Thoughts? Thanks for any and all help.

 

[Guest]

Turn on the macro recorder and do it manually.

As long as they are structured as Tables, what is recorded should be easily
adaptable.

I recorded:
ActiveSheet.ListObjects.Add(xlSrcRange, _
Range("$D$1:$I$100"), , xlYes).Name = "List1"

so you could do something like

Sub CreateLists()
Dim sh As Worksheet
Dim rng As Range
For Each sh In ThisWorkbook.Worksheets
Set rng = sh.Range("A1").CurrentRegion
sh.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = _
Replace(sh.Name, " ", "") & "_" & "List1"
Next
End Sub