copy specific ranges based on certain values

**WhiteKelso** · 01-06-2008, 09:09 AM

Hi all, and Happy New Year:

I'm here again to seek help. Here's my scenario:

My sheet 1 has the values in column A to G; column A only contains 0 or 1.
I'm looking for a vba code to copy TO a new worksheet the values in columns B to G (they contain results of formulas within the worksheet) IF the value of A is 1. Column A looks like this:
A B C D E F G
0
0
1
1
1
0
0

I'm hoping for the usual help. Many thanks.

WK

**VBA Noob** · 01-06-2008, 09:28 AM

Try something like

Dim Rng As Range
Dim DestRange As Range
Set DestRange = Sheets(2).Range("A" & Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1)
Set Rng = Range("A1")
    Rng.AutoFilter Field:=1, Criteria1:="1"
    Rng.Offset(1, 0).Resize(Cells(Rows.Count, "A").End(xlUp).Row, _
    Cells(3, 7).End(xlUp).Column).Copy _
    Destination:=DestRange
ActiveSheet.AutoFilterMode = False

See link for ideas on tidying it up

http://www.rondebruin.nl/copy1.htm

VBA Noob