Macro turning colums w/headers into list on multiple sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top