VBA Macro has begun hiding rows instead of copying down.

[Steady]

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

 

[Twollaston]

Okay steady, the rows aren't hidden. The data is filtered. In column F there is a filter that has "all ords" removed. Just remove the filter.
The reason it keeps hiding the rows, is because the filter is still on

P.S. this is a really cool workbook, so nice job to whoever built it. Nice way to build a watchlist

 

[Twollaston]

Okay steady, the rows aren't hidden. The data is filtered. In column F there is a filter that has "all ords" removed. Just remove the filter.
The reason it keeps hiding the rows, is because the filter is still on

The macro can't run correctly with the filter on
you can add this in the beggining of your code after the sub name and that will always turn the filter off first. Might want to put that on all your buttons that load the data
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

Sub consumer_Click()
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False


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

 

[Steady]

Thanks for all your help! I think i found the problem. I was copying the workbook and slightly changing the name. For some reason with all the code in the sheets it didn't like it. I took your advice and put everything into modules and now it seems to work perfectly. :D

 

[Steady]

Ok great thanks guys, i didnt see these comments before posting and then it stopped working again.
Thanks i built this myself and it took me a very long time with such little knowledge. It makes analysing fundamental data much less time consuming.

Honestly i can't thank you both enough. This is my second job and without these spreadsheets working it makes my life incredibly stressful!

 

[Twollaston]

Honestly i can't thank you both enough. This is my second job and without these spreadsheets working it makes my life incredibly stressful!

Well good work, it's pretty cool. and no problem happy to help you.

I did have one idea that can speed it up for you. You could have a master button, that runs the code to grab the the data and load all the sheets in 1 click. Simply by just consolidating the code under an extra button. You would just need to change the "ActiveSheets" to the destination sheet names. And then you could still have your original buttons in place if you need them.

and then just place this at the beginning
Application.ScreenUpdating = False

and this at the end
Application.ScreenUpdating = True

which will stop the screen flicker and the page switches and speed it up a little.

 

[Steady]

Ok great, thanks for the ideas! I will give them a go when i have some spare time and hopefully that will speed the process up even further :D