Dynamic row copy with condition

[Hornet222]

HI everyone,

I just got started with VBA and I have a challenging macro to program. I would appreciate if you could help me or give me a hint how to do the following.

I have a table that looks like this:

prices quantities Company
1 5 ABC Company
3 45 ABC Company
4 6 ABC Company
6 3 CDF Company
34 2 GHI Company
43 4 GHI Company

I would like to copy all rows (A1:C3) for ABC company in one worksheet, all rows for CDF in another worksheet and all rows for GHI company in a third worksheet. Here is the catch. Every week this table is updated and the names and frequence of the company change. For example in week two there is only one row for ABC company, no CDF company and 5 rows for GHI company. Is there any chance to code that in VBA?

I started and I created the following:

Sub Copy_data()

Workbooks("data 20110427.xls").Sheets("Refined data").Activate

If Range("C" + i) = Range("C" & (i + 1)) Then
Range("A" & i & ":C" & i).Select
Selection.Copy
Workbooks("M.xls").Activate
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next
End Sub

However this setup far from complete and it would ignore the last row of every company.

Any ideas?

Thanks a lot!

 

[Armando Montes]

Check this out:
http://www.contextures.com/AdvFilterRep.zip

 

[Hornet222]

Thanks. Your file was what I was looking for!

Thanks again