Sorting multiple lists into one

[bibbyd01]

Hi All

I'm using excel 2003. I have a main summary sheet with a list of names that I use for lookup data. I also have several other sheets that are updated regularly, with a list of names and figures. If a new name is added, I currently have to sort the list, and do some form of check to see what's missing using a Vlookup, then copy the name across to the summary sheet.

Is there some marco I can run at the end of each day to copy it across to the main sheet, checking the main sheet to see if the name already exists,and copying across any name that's missing I can construct a macro to do the sorting afterwards.

 

[Sandeep Warrier]

This should help. Please note that if you have many sheets & many rows of data in a sheet, the code will take time to complete. This code assumes that the summary sheet is the 1st sheet and the list of names in all sheets start from cell A1. You can change the ranges to suit your needs.

Sub UpdateSummaryList()
 
    Dim stListName As String
    Dim i As Integer
    Dim rCell As Range, rFullRange As Range, rng As Range
    
    For i = 2 To ThisWorkbook.Sheets.Count
    
        Sheets(i).Select
        
        Set rFullRange = Range("A1", Range("A65536").End(xlUp))
        
        For Each rCell In rFullRange.SpecialCells(xlCellTypeVisible).Cells
        
            stListName = rCell.Value
            Sheets(1).Select
            Range("A1").Select
            With Sheets(1).Range("A:A")
            
                Set rng = .Find(What:=stListName, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
            
                If Not rng Is Nothing Then
                
                    GoTo 12345678
                    
                Else
                
                    If ActiveCell.Value <> "" Then
                    
                        If ActiveCell.Offset(1, 0).Value <> "" Then
                        
                            ActiveCell.End(xlDown).Offset(1, 0).Select
                            ActiveCell.Value = stListName
                            
                        Else
                        
                            ActiveCell.Offset(1, 0).Select
                            ActiveCell.Value = stListName
                            
                        End If
                        
                    Else
                    
                        ActiveCell.Value = stListName
                        
                    End If
                    
                End If
                
            End With

12345678:

        Sheets(i).Select

        Next rCell
    
    Next i
 
End Sub

 

[bibbyd01]

thanks for that

It works exactly as I need, and I understand how most of it works, so I can modify it and learn to use it for what I need. I'm going to have a bit of a play as there's a couple of other things I need to add in (like changing it to find the last cell in the range and adding a row there before pasting the value into it).

If I get any more problems then I'll come back and wave my panic flag!