Copy Data in a selected range based on criteria

ksstormhunter · Mar 4th 2015

I have a range of data from C10:AA120 and I need to only copy over only the rows of data that have in Column AA the word "Passed"

**PCI** · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

Where do you want to paste the data?

ksstormhunter · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

The data will go into Sheet6 Cell F5

**PCI** · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

Is there header for the data to be filtered

ksstormhunter · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

No there are no headers for the data

**PCI** · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

Perhaps next code

Code

Option Explicit


Sub CopyData()
Dim WkRg As Range
Dim SelectRg  As Range
Dim F  As Range
Dim DestRg As Range
Dim IFlg  As Boolean
    Set WkRg = Range("AA10:AA120")
    Set DestRg = Sheets("Sheet6").Range("F5")
    For Each F In WkRg
        If (F.Value = "Passed") Then
            If (IFlg) Then
                Set SelectRg = Union(SelectRg, Range(Cells(F.Row, "C"), Cells(F.Row, "AA")))
            Else
                IFlg = True
                Set SelectRg = Range(Cells(F.Row, "C"), Cells(F.Row, "AA"))
            End If
        End If
    Next F
    SelectRg.Copy Destination:=DestRg
End Sub

Display More

ksstormhunter · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

So basically I need to search in Column AA for the word "Passed", if yes then take that data on that fow from Column C to Z and paste into Sheet 6 Cell F5, then perform the same check on the next row down and so forth.

**PCI** · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

A variation

Code

Option Explicit


Sub CopyData()
Dim WkRg As Range
Dim SelectRg  As Range
Dim F  As Range
Dim DestRg As Range
Dim IFlg  As Boolean


    Set DestRg = Sheets("Sheet6").Range("F5")
    With Sheets("Sheet1")
        Set WkRg = .Range("AA10:AA120")
        For Each F In WkRg
            If (F.Value = "Passed") Then
                If (IFlg) Then
                    Set SelectRg = Union(SelectRg, Range(.Cells(F.Row, "C"), .Cells(F.Row, "Z")))
                Else
                    IFlg = True
                    Set SelectRg = .Range(.Cells(F.Row, "C"), .Cells(F.Row, "Z"))
                End If
            End If
        Next F
    End With
    SelectRg.Copy Destination:=DestRg
End Sub

Display More

ksstormhunter · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

On the SelectRg.Copy Destination:=DestRg line i am getting Ojbect variable or With Block variable not set error.

ksstormhunter · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

On the first Sub routine it is pasting the data that is on sheet6 onto sheet 6, instead of taking the data from sheet 4 where my data is currently on.

**PCI** · Mar 4th 2015

Re: Copy Data in a selected range based on criteria

See with file attached how it is
Else, please prepare a short sample removing personal data, it will help to debug

Copy Data in a selected range based on criteria

Participate now!

Share