Announcement

Collapse
No announcement yet.

Consolidate Lists From Multiple Sheets Into One

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Consolidate Lists From Multiple Sheets Into One

    What I'm trying to chieve is the following..
    On about 30 worksheets I have lists of names. These names are changed and added to now and then.

    I would like them to automatically populate a worksheet called "list". Preferably the names of every sheet with a blank cell at the bottom.

    The whole work book is about 60 worksheets.

    Thanks
    I have attached a smalle example with the exact rows
    Attached Files

  • #2
    Re: List Entries On Other Pages

    Hi,

    Try,

    Sub NameList()
    Dim a, w(), ws As Worksheet, i As Long, n As Long, lSht As Worksheet

    Const CommonName As String = "Group"

    ReDim w(1 To 5000, 1 To 1)
    For Each ws In ActiveWorkbook.Sheets
    If InStr(1, ws.Name, CommonName, vbTextCompare) Then
    With ws
    a = .Range("a2", .Range("a78").End(xlUp).Offset(1))
    End With
    For i = 1 To UBound(a, 1)
    n = n + 1
    w(n, 1) = a(i, 1)
    Next
    Erase a
    End If
    Next
    On Error Resume Next
    Set lSht = Sheets("List")
    On Error GoTo 0
    If lSht Is Nothing Then
    Set lSht = Sheets.Add: lSht.Name = "List"
    End If
    With lSht.Range("a1")
    .Value = "INDIVIDUAL NAMES"
    .CurrentRegion.Offset(1).ClearContents
    .Offset(1).Resize(n, 1).Value = w
    .Columns(1).AutoFit
    End With
    End Sub


    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: List Entries On Other Pages

      Thanks

      Comment

      Working...
      X