Posts by jamesttu1821

I am trying to modify the criteria of this code to only copy rows from the Raw Data sheet that meet this criteria, If Column 5 or 6 is below 10 copy the row but If Columns 4, 5 and 6 = 0 then do not copy the row. Any help would be greatly appreciated.

Dim x, y, i&, ii&, iii&


x = Sheets("Raw Data").Cells(1).CurrentRegion
ReDim y(1 To UBound(x, 1), 1 To 12)
For i = 2 To UBound(x, 1)
If x(i, 6) <= 10 Or x(i, 5) <= 10 Then
ii = ii + 1
For iii = 1 To 12
y(ii, iii) = x(i, iii)
Next
End If
Next
With Sheets("Hot List")
.Rows(2).Resize(100000).Delete
.Cells(2, 1).Resize(UBound(y, 1), 12) = y
.Columns(4).Resize(, 6).NumberFormat = "#,###"
.Columns(11).NumberFormat = "#,###"
End With


Range("A2").Select

Marius44, that worked. Thank you very much.

Ultimately, the Hot List will erase itself at each update. Each week when a new report comes out, the old Raw data will move to a new sheet automatically. The when I populate the raw data and hit update on the Hot List sheet the old data on the hot list sheet will disappear and be repopulated with the New Data from the Raw Data Sheet. This I know how to write, I just haven't gotten to that step yet. So far the only thing I am struggling with is copying only Columns A:L for rows that meet the criteria and moving it to the hot list. Any Help would be greatly appreciated.

Roy, I attached my workbook. I am sure that there is a lot better way to do this. I am very new to programming Excel so I know it is probably very poorly written. Any help would be greatly appreciated. The workbook is not finished, there are still more things I need to get to do, currently it creates the Hot List based off the raw data, verifies if it was on the list the previous week and is still on the list or has fallen off, and sorts the list. The issue I am having is I do not need all the columns that are in the raw data to go to the hot list sheet. I just need Columns A:L if the criteria is met.

I have written this code to copy a row if the condition is met. However, It was decided that not all the data from the Row is needed, how would I only copy Columns A:L for each row that meets the condition and put it into the second worksheet as shown.

Worksheets("Raw Data").Activate


a = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a


If Worksheets("Raw Data").Cells(i, 6).Value <= 10 Then


Worksheets("Raw Data").Rows(i).Copy
Worksheets("Hot List").Activate
b = Worksheets("Hot List").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Hot List").Cells(b + 1, 1).Select
ActiveSheet.Paste


End If