Copy rows based on a condition/rule using VBA

[sandor jeges]

This is my current code can anyone help to make it so all rows from the performanceEQ sheet to copy to Output with the rule copy row over if there is a data in cell B2 down, then when there is no more data in the B Column, copy the same row but with the rule IF there is data in cell D2 down until there is no more data - Can anyone help!!!!???

Sub Stocktransfer()
Set i = Sheets("PerformanceEQ")
Set e = Sheets("Output")
Dim d
Dim j
d = 1
j = 2
 
'FOR THE SELLER
 
Do Until IsEmpty(i.Range("B" & j))
 
If i.Range("B" & j) = "VEAEWP" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
 
End If
j = j + 1
Loop
 
'FOR THE SELLER
 
Do Until IsEmpty(i.Range("D" & j))
 
If i.Range("D" & k) = "VERGRE" Then
d = d + 1
e.Rows(d).Value = i.Rows(d).Value
 
End If
j = j + 1
Loop
 
End Sub

 

[Smitty]

Welcome to the Board!

It's generally a bad idea to duplicate data with copy/paste. You can easily keep the data together (think like a database), then use PivotTables to summarize based on your rules.

 

[My Aswer Is This]

Assuming you have "VEAEWP" in column "B"
And "VERGRE" in column "D"
I'm sure there is another way to do this but I do not know it.
Try this:

Sub Filter_Me()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Output").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 4))
        
    .AutoFilter Field:=1, Criteria1:="VEAEWP"
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Output").Range("A" & Lastrow)
    .AutoFilter
    End With
With ActiveSheet.Range(Cells(1, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 4))
Lastrow = Sheets("Output").Cells(Rows.Count, "A").End(xlUp).Row + 1
    .AutoFilter Field:=3, Criteria1:="VERGRE"
    .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Output").Range("A" & Lastrow)
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

 

[sandor jeges]

Thanks Smitty - I personally wanted to see if VBA would work - i do have a colleague saying SQL might work but i know very very little on SQL
Thanks for the response My Answer is This. ALthough i am getting a Debug issue on the line ".Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Output").Range("A" & Lastrow)"

Any thoughts on why?

 

[My Aswer Is This]

Do you have a sheet name:
"Output"

And do you have the values you mentioned in the columns "B" and "D"

All these values must be spelled exactly correct.

 

[sandor jeges]

Worksheet is called Output but I should of noted that the VEAEWP and VERGRE start in row 2 - i have headers which are B1 = Sell Portfolio and D1 = Buy Portfolio

 

[sandor jeges]

Sorry all good it works - thanks My answer is this!

Sorry another question how much harder would it be to rearrange and reduce the columns copied over to the output tab?

 

[My Aswer Is This]

Header row does not matter.

And you have:
"VEAEWP" in column "B"

and "VERGRE" in column "C"

 

[My Aswer Is This]

So now your saying it works?? What was wrong?

What columns do you want copied over?

 

[Smitty]

Thanks Smitty - I personally wanted to see if VBA would work - i do have a colleague saying SQL might work but i know very very little on SQL

I completely get it, VBA's awesome, and it's saved me thousands of hours over the years. My point was not about that though, but not duplicating data in the first place. If it exists in one place in your workbook, it doesn't need to be copied somewhere else.

As for SQL, that's a completely different story, and if you need a database or not.