Copy Question Variation

Posted by Andonny on December 29, 2000 8:52 PM

Hi again,
Thank you everybody for the help in the past. I have been very lucky that you all have been so nice to me.
I need to copy all rows which have 101.3311 and FOD in the same row (culumn B and C) from sheet1 into sheet2.
Sample:
.......A...........B..........C
1....Apple.......101.3311....FOD
2....Kiwi........101.3311....FOD
4....Beer........101.4411....BEV
5....Wine........101.4411....BEV
6....Banana......101.3311....FOD
7....Orange......101.3311....FOD
8....Tomato......101.3311....FOD
9....Champagne...101.4411....BEV
10...Kiwi........101.3311....FOD
11...Beans.......101.3311....FOD
12...Vodka.......101.4411....BEV
13...Kiwi........101.3311....FOD

Thanks for your help in advance
Andonny

Posted by Dave on December 29, 2000 11:21 PM

Hi Andonny

Try this code:
Sub SpecialCopy()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
[A1].Select
If Sheets("Sheet1").AutoFilterMode &LT;&GT; True Then Range("B1:C1").AutoFilter
With Selection
.AutoFilter Field:=1, Criteria1:="101.311"
.AutoFilter Field:=2, Criteria1:="FOD"
End With

Range("B1").CurrentRegion.Offset _
(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Cells(1, 1)
Range("B1:C1").AutoFilter
Application.ScreenUpdating = True
End Sub

You could of course do this manually by applying Autofilters to B1:C1 then filter column B to show only 101.3311 and Column C to show only FOD.

Then With any cell within the list selected, push Ctrl+Shift+*(asterisk) then F5>Special/Visible cells>Ok, then Shift+Spacebar, Ctrl+C and paste where you want it.

Dave

OzGrid Business Applications