Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Consolidate Lists From Multiple Sheets Into One

  1. #1
    Join Date
    26th January 2003
    Location
    New Zealand
    Posts
    32

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: List Entries On Other Pages

    Hi,

    Try,

    VB:
    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

  3. #3
    Join Date
    26th January 2003
    Location
    New Zealand
    Posts
    32

    Re: List Entries On Other Pages

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: March 8th, 2008, 01:59
  2. Consolidate Data From Multiple Sheets & Workbooks
    By Barnzy in forum Excel General
    Replies: 6
    Last Post: October 2nd, 2007, 09:50
  3. Consolidate Sheets Of Same Name From Multiple Workbooks
    By xlnewbie in forum Excel General
    Replies: 9
    Last Post: September 19th, 2007, 07:03
  4. Consolidate data from multiple sheets into 1
    By ascalese in forum Excel General
    Replies: 6
    Last Post: January 7th, 2005, 01:03
  5. Consolidate data from multiple sheets into one sheet.
    By hulettwyo in forum Excel General
    Replies: 3
    Last Post: April 11th, 2003, 03:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno