In VBA I try to run a loop with an if statement. The loop is set to run a row at a time for a range (wks "Data", B7:J25).
For each row if the value at column C7:C25 is 1, I would like to copy that row (e.g. B7:J7) and insert it at the worksheet "temp" one at a time.
I have tried various codes, for example:
Sub start()
Dim i As Integer
Dim wsData, wsCalcAndOutput, wsTemp As Worksheet
For i = 1 To 25
If Cells((7 + i), 3) = "1" Then
Worksheets("Data").Range("B7:J7").Copy _
Worksheets("temp").Range("B7:J7")
End If
Next
End Sub
But then I can only copy and paste the first row of the range. Alternatively, I found this procedure at stackoverflow, but I can't seem to be able to paste what I copy at each iteration:
Dim wsData, wsCalcAndOutput As Worksheet Dim rSPX, rSX5E, rNKY, rUKX, rSMI, rEEMUP, testData As Range
Sub start()
Dim i As Integer
For i = 1 To 25
If Cells((7 + i), 3) = "1" Then
With ActiveSheet
.Range(.Cells((7 + i), 2), .Cells((7 + i), 10)).Copy
End With
End If
Next
End Sub
Is this the right way to do so or is there a more efficient way?
Also - in the dataset the criteria for the if statement is actually a string called either "TRUE" or "FALSE". Can an if statement use a string as a signal instead of "1"?
All the best, Christoffer
As BigBen says, using AutoFilter would be quicker but here is one way of doing this with a loop. Have added a few comments which hopefully explain the basics.
One problem with your code was that you weren't changing the destination cells so they would continually be overwritten.
Sub start()
Dim i As Long 'better than integer
Dim n As Long: n = 7
Dim wsData As Worksheet, wsCalcAndOutput As Worksheet, wsTemp As Worksheet 'specify each type
With Worksheets("Data")
For i = 7 To 25 'change as appropriate
If .Cells(i, 3) = 1 Then 'no need for quotes
Range(.Cells(i, "B"), Cells(i, "J")).Copy _
Worksheets("temp").Cells(n, "B") 'start at row 7?
n = n + 1 'update so that we don't overwrite next time
End If
Next
End With
End Sub