Filter in VBA

[tlindeman]

Is it possible to copy records from one tab to another tab based on a filter. In other words, I need to copy from tab "Report" Columns A:C and paste all rows where Column C = 10 and then paste into tab "Dept 10 Accounts" , then copy from tab "Report" Columns A:C and paste all rows where column C = 20, then paste into tab "Dept 20 Accounts" . Please supply code or let me know that is not possible.

Thank you
Tony

 

[Smitty]

What happens if you record a macro, apply the filter(s) you need and copy?

If it works for a sample set, then post your code and someone can set it up dynamically for you.

 

[tlindeman]

Smitty,
Thank you that is a good idea. Here is the code using the recorder.

' Departments Macro
'

'
ActiveSheet.Range("$A$5:$P$103").AutoFilter Field:=3, Criteria1:="10"
Range("A5:C71").Select
Selection.Copy
Sheets("Dept 10 Accounts").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("Report").Select
Range("A62").Select
End Sub

Thank You
Tony

 

[Smitty]

OK, not tested, but this should give you an idea:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br><SPAN style="color:#007F00">'   Departments Macro</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    i = Application.InputBox("Please enter the Department #", "Department #", Type:=1)<br>    <br>        <SPAN style="color:#00007F">With</SPAN> ActiveSheet.Range("$A$5:$P$103")<br>            .AutoFilter Field:=3, Criteria1:=i<br>            .SpecialCells(xlCellTypeVisible).Copy Sheets("Dept " & i & " Accounts").Range("A5")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[tlindeman]

Smitty,
It worked perfect. I started building it the long way, ie, 4 lines of code per department, your way works much better. Thank you for your time.

Tony