Filtering Data and copying only filtered results

**bigfishprf** · 03-04-2013, 01:21 PM

I want to filter the data in one Workbook based on "Department Number" which produces about 10 lines which match the filter but these are on non-sequential lines within the data. I then want to copy only these 10 lines so that I can paste them into another Workbook.
Can anyone help me please?

**AKK9** · 03-04-2013, 01:26 PM

Highlight the cells, Ctrl + G, Visible Cells Only, OK.

Now only the visible cells are selected, you can copy and paste to another workbook.

Does this help?

**bigfishprf** · 03-05-2013, 05:26 AM

Many thanks for your suggestion. I am trying to do this within a macro so I think the VB solution I have been given will be better. Thanks again

**JOHN H. DAVIS** · 03-04-2013, 02:25 PM

This would assume your criteria is in Column A.

Sub bigfishprf()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("A2:A" & lr)

    .AutoFilter 1, "Department Number"
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("another workbook.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
    
End With

ActiveSheet.AutoFilterMode = False

End Sub

**bigfishprf** · 03-05-2013, 05:28 AM

Hi John

Thanks for your help. It all works perfectly. I am a novice to VB so trying to teach myself in bits. The code you have given me is part of my answer and I will now work on the rest....... I might be back for some more help later tho

Cheers

**bigfishprf** · 03-05-2013, 06:41 AM

Hi John, I said I might need more help.....
If I want to filter the "Department Number" based on a vale in a specific cell on a different sheet in the workbook, how can I do this?

Hope you can help

Paul

**JOHN H. DAVIS** · 03-05-2013, 07:59 AM

Not sure I understand but if I do try this:

Sub bigfishprf()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("A2:A" & lr)

    .AutoFilter 1, Sheets("Different sheet").Range("A1").VALUE
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("another workbook.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
    
End With

ActiveSheet.AutoFilterMode = False

End Sub

**bigfishprf** · 03-05-2013, 09:35 AM

Thanks John, you understand perfectly. I had a , before the Range rather than a . which is why it would not work!

Any chance I can ask 2 questions?
1. In your original code, the paste into the other workbook is perfectly located at cell A107. How does your code know where to paste it?
2. If I wanted to wrap some code around it so that the macro loops round until the Value in the Filter Cell in the other Worksheet ="XXX" at which point it should finish. Is that something you could help me with? Current code shown below

Again, many thanks

   Sub bigfishprf()
' Section 1 - Copy Stores'
'
    Sheets("Stores").Select
    Range("A2:A500").Select
    Selection.Copy
    Range("b2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Staff").Select
'
'  Section 2 - Open Timesheet Master Template
'  I WOULD WANT TO CHECK HERE IF Sheets("Stores").Range("B2").Value = "XXX".  If it does
' then repeat the rest of the code in the Macro 1 more time.  If it does ="XXX" then
' the processing is complete
'
    Workbooks.Open Filename:= _
        "F:\PRF Docs\Grainstore Ltd\Antler\FINAL TEMPLATES\Retail Timesheet MasterTemplate.xlsx"
    Windows("Retail Staff List.xlsm").Activate
'
'  Section - 3 - Create Timesheet for next store in list
'
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("A2:A" & lr)

    .Autofilter 7, Sheets("Stores").Range("B2").Value
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("Retail Timesheet MasterTemplate.xlsx").Sheets("Parameters").Range("A" & Rows.Count).End(3)(2)
    
End With

ActiveSheet.AutoFilterMode = False
 Rows("1:1").Select
    Selection.Autofilter
' Section 4 - Prepare Store List for next store
'
 Sheets("Stores").Select
    Range("B3:B500").Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

**JOHN H. DAVIS** · 03-05-2013, 10:22 AM

This line places the copied row to the next available row.

Workbooks("Retail Timesheet MasterTemplate.xlsx").Sheets("Parameters").Range("A" & Rows.Count).End(3)(2)

I'm very busy at the moment running my monthly reports and won't be able to comply with your other requirement. If no one else helps then later I'll take a look at it.