Hello all,
Can anyone help with the issue i am currently having on my excel spreadsheet? The below code was working perfectly fine but when i close it and re-open the file it starts causing errors. The code is designed to search a large list on another sheet and if found copy the entire row to the current sheet.
For some reason now it's copying the rows but hiding most of them and then deleting them. You can see in the attached snapshot that row 15 is hidden, then it deletes everything after that row it finds (There should be 79 rows) I've never had this problem before and cannot seem to fix it, any help would be greatly appreciated.
Sub consumer_Click()
Range("A2:D300").ClearContents
a = Worksheets("All Ords").Cells(Rows.Count, 1).End(xlUp).row
For i = 2 To a
If Worksheets("All Ords").Cells(i, 3).Value = "Consumer Discretionary" Then
Worksheets("All Ords").Rows(i).Columns("A:D").Copy
Worksheets("Consumer Discretionary").Activate
b = Worksheets("Consumer Discretionary").Cells(Rows.Count, 1).End(xlUp).row
Worksheets("Consumer Discretionary").Cells(b + 1, 1).Select
ActiveSheet.Paste
End If
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Consumer Discretionary").Cells(1, 1).Select
Next
End Sub
Can anyone help with the issue i am currently having on my excel spreadsheet? The below code was working perfectly fine but when i close it and re-open the file it starts causing errors. The code is designed to search a large list on another sheet and if found copy the entire row to the current sheet.
For some reason now it's copying the rows but hiding most of them and then deleting them. You can see in the attached snapshot that row 15 is hidden, then it deletes everything after that row it finds (There should be 79 rows) I've never had this problem before and cannot seem to fix it, any help would be greatly appreciated.
Sub consumer_Click()
Range("A2:D300").ClearContents
a = Worksheets("All Ords").Cells(Rows.Count, 1).End(xlUp).row
For i = 2 To a
If Worksheets("All Ords").Cells(i, 3).Value = "Consumer Discretionary" Then
Worksheets("All Ords").Rows(i).Columns("A:D").Copy
Worksheets("Consumer Discretionary").Activate
b = Worksheets("Consumer Discretionary").Cells(Rows.Count, 1).End(xlUp).row
Worksheets("Consumer Discretionary").Cells(b + 1, 1).Select
ActiveSheet.Paste
End If
Application.CutCopyMode = False
ThisWorkbook.Worksheets("Consumer Discretionary").Cells(1, 1).Select
Next
End Sub
Last edited by a moderator: