Macro suddenly no longer works

When it highlights that line, what is the error number and error description that are shown?

Note that if I were to move my debug into the next row of the code and run it, the macro will run completely fine.

I'm not following how you can do that. Once you hit an error, if you hit F8 to continue you will always* get the same error and not be able to proceed. And you can't use the debugger to start execution in the middle of a sub.

______________________________
*There are probably very rare exceptions.

There shouldn't be a space on that line between Sheets and ("My Sheet Name"). And select would normally have a capital S.

What error code are you getting? Could any of the sheet names have changed?

Post ALL the code, ideally in a sample/redacted workbook.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

I tried it again, and this time it's giving me a different error.

However, the same "Run-time error 1004" code. But this time it's showing on the 'ShowAllData' method, instead of the .select

To answer your question on the F8, all I did here was that I left click and hold the arrow of the debug that was highlighted and move it to the next line of code and then run it again F5.

Error Message.png

Debug.png

@6SJ: you can drag the highlight down to the next row and continue execution from there.

Originally Posted by TMS

@6SJ: you can drag the highlight down to the next row and continue execution from there.

Well damn, you learn something new every day. I can't believe I I didn't know that in the number of years I've been using VBA.

Hello,

so according to your red writtings it can't be the code - even if IMO badly coded - but something else
then without the necessary it's just a guessing challenge
so you have more chance to find out yourself with checking each name, each variable content, …
Maybe just restarting from a backup.

It worked. Now it doesn't. Something HAS changed. You've not changed the code. Has/have the sheet name(s) changed? If it doesn't like the sheet names and it doesn't like the Tables, is the right workbook open?

First suggestion. Save all your workbooks, shut down Excel and just start Excel and test the Excel application. Test. If it works, good. If it doesn't, save ALL your work, close ALL your applications, shut down the machine. Start it up again. Just start Excel and test the Excel application.

This isn't causing your problem (probably) but it's generally error-prone to manage sheet operations by selecting/activating a sheet then relying on ActiveSheet to refer to it. It's better to explicitly qualify references, which could be done nicely here using a With statement. Same goes for selecting ranges then referring to Selection.

@6SJ: main reason for asking for the code is/was to see that kind of issue.

Could be as simple as the workbook saved with a different sheet selected than is the norm and the coding expecting to start with one thing and getting another. Again, not here, given the selection, but the prior code may not have done something it was intended to. Maybe we'll never know.

And thanks for the rep, by the way

Using Activate is the same problem.

Just as an example, instead of this:

Sheets("Sheet1").Select
ActiveSheet.ListObjects("List1").Range.Select
Selection.ListObject.QueryTable.Refresh Background Query:=FaLse
AciveSheet.ShowAllData

Do this:

With Sheets("Sheet1")
   .ListObjects("List1").QueryTable.Refresh BackgroundQuery:=False
   .ShowAllData
End With

These lines of code are probably not causing your problem but something could be happening upstream that you're not showing us that causes it.