Copy data from 3 worksheets into 1 master file

[DEL]

Hi,
I would like to copy data from 3 large source files (1 sample template attached) which are downloads from operating system.
The 3 reports are all the same format but have a varying number of line entries & headers on download. I want to try merge these onto new sheet.
I then need to sort by a particular column and then delete the rows that I don't need after sort.
Can I enter code that will sort report by particular characteristics, eg I only need numbers beginning with '1' in column Q and need to delete all other rows

any help would be appreciated,
D

 

[Derek McGill]

Try this code to sort and delete non 1 rows,

Sub SortColQ()
Dim Lastrow As Long

With ActiveSheet
Lastrow = .Cells(.Rows.Count, "Q").End(xlUp).Row
End With

    Range("Q13:Q" & Lastrow).Select
    Application.AddCustomList ListArray:=Array("1")
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("Q13:Q" & Lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="1", _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("Q13:Q" & Lastrow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Call RemoveNon1
End Sub
Sub RemoveNon1()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = 13 '.UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "Q")

                If Not IsError(.Value) Then

                    If .Value <> "1" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub