Loop through cells and copy if condition is met

[MikkelDuif]

Hi,

I am new to VBA, and have searched the forum a lot, but I couldn't find a solution that exactly matches what my code requires.

What I basically want to do, is to loop through a lot of cells in a column, and if a condition is met, I want to copy the two cells next to it in to another sheet, and paste values.

The sheet updates automatically from an online table, so I later need to make it repeat the process all the time.

Here is an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A: Condition[/TD]
[TD]B: Name[/TD]
[TD]C: Price[/TD]
[/TR]
[TR]
[TD]now[/TD]
[TD]Name 1[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]now[/TD]
[TD]Name 5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]now[/TD]
[TD]Name 6[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]later[/TD]
[TD]Name 9[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

So, the table updates automatically with a connection from my company, and the condition is calculated from criterions using the IF formula. But what I want to get my code to do, is that, if A says now, then I want to copy B and C in the second row to a new sheet. If it says later, it should just skip it.
Sadly, I cannot use the lookup function without code, as the data updates, and I need to get the prices as the condition is met, and get them into 'static text' that is not based on any function (such that my new table doesn't change all the time, as the data updates).

Hope someone can help me a bit. I have watched a few tutorials as well, but couldn't find anything that exactly matched this, and was not able to modify coded, such that it matched.

 

[SQUIDD]

Hi

Have you considered filtering column A for "NOW", then just copying the filtered range to the new sheet.

I am happy to help with a loop if the above in not acceptable.

Dave

 

[oldbrewer]

for j=2 to 10
if cells(j,1)="now" then ...code to copy paste
next j

another way

if cells(j,1)="red" then sheet2!cells(j,1)=cells(j,2)

needs a bit of tweaking but the principle sholud be clear

 

[SQUIDD]

Sub filter_me()

With Sheets("SHEET1")
    .Range("A:C").AutoFilter Field:=1, Criteria1:="now"
    .AutoFilter.Range.Copy
    End With
With Sheets("SHEET2")
    .Range("B1").PasteSpecial
    End With
With Sheets("SHEET1")
    ActiveSheet.Range("A:D").AutoFilter
    End With
End Sub

DAVE

 

[MikkelDuif]

Sub filter_me()

With Sheets("SHEET1")
    .Range("A:C").AutoFilter Field:=1, Criteria1:="now"
    .AutoFilter.Range.Copy
    End With
With Sheets("SHEET2")
    .Range("B1").PasteSpecial
    End With
With Sheets("SHEET1")
    ActiveSheet.Range("A:D").AutoFilter
    End With
End Sub

DAVE

Hi, thank you very much.
I think I figured it out, however, I am not sure if my code is optimale, since I am quite new.
This is the solution I figured out:

Sub Copy()


Dim i As Integer
Dim j As Integer
j = Application.CountA(Worksheets("Sheet1").Range("Table1[now/later]"))


For i = 2 To j


Sheets("Sheet1").Select
Cells(i, 2).Select
If ActiveCell = "now" And Cells(i, 3) > 0 Then
Cells(i, 1).Copy
Sheets("Sheet2").Select
Cells(1, 2).Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.PasteSpecial xlPasteValues
Sheets("Sheet1").Select
Cells(i, 3).Copy
Sheets("Sheet2").Select
Cells(1, 3).Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.PasteSpecial xlPasteValues


End If


Next




End Sub

 

[MikkelDuif]

Hi,

I am not sure that the autofilter is a good solution, as my data updates.
However, I guess that the autofilter does that as well.

Currently, my data is not updating, as the table I'm pulling data from is not updating right now, so I cannot check it.

I have provided a solution so far below.

Thanks for your helpful suggestion!

 

[MikkelDuif]

for j=2 to 10
if cells(j,1)="now" then ...code to copy paste
next j

another way

if cells(j,1)="red" then sheet2!cells(j,1)=cells(j,2)

needs a bit of tweaking but the principle sholud be clear

Thank you very much! With this and a little googling, I found a solution which I have posted above.