Macro that worked for ages stopped working [Very inexperienced user]

Good afternoon,

I have a macro (below) that I use for work often; it takes a specific sheet, copies it into a new file as values only, saves it in a file location, and moves on to the next sheet.

A piece of the macro is as follows (it repeats ~40 times with different Client names):

Please Login or Register  to view this content.

and repeat with the next client in the list. The tabs are ordered and the names match identically. We have used this macro for years. Now when running, I receive the rather generic:

Run-Time error '1004':
Method 'SaveAs' of object '_Workbook' failed

Other queries of this same error haven't proven too useful as everyone seems to use the SaveAs part differently and I'm not terribly experienced with macro syntax to make sense of it and I'm hoping someone here can help. The debug option highlights the following as the issue:

"F:\Corporate Reports\CLIENT\2020\12.31.20 LB Report - CLIENT.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

To make this more interesting, after I've run the macro (remember, there are 40ish of the above code chunks, rinse and repeat style), the previous one works (I can open the file and everything) and it fails on the next. Once I've run the macro ~40 times, it then can go all the way through with no errors? It seems to be per workbook though, as we use this style macro in several books and I really don't want to have to run this 40 times every month when I have a new batch to save.

Maybe permissions on the file/folder location(s)? Or maybe a lack of space on the drive?

You repeat the code 40 times? Why don't you create a loop?

You should really avoid selecting objects like sheets and cells. And there seems to be some redundancy ...

Please Login or Register  to view this content.

Could probably be just:

Please Login or Register  to view this content.

And this:

Please Login or Register  to view this content.

Could be just this:

Please Login or Register  to view this content.

Oh, and ...

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)

Maybe, for some reason, the sheet(s) became bloated. And, maybe now they're not. So, once a bloated file has been overwritten, it makes space.

Maybe there are a lot of temporary files on the drive that could be cleared down?

Rather than repeat the code 40 times, it might be an idea to create an array with the Client sheets and loop through that. Much easier to maintain. The basic code would never need to change, you just add a new client on to the end of the list.

I'll try and put something together as an example tomorrow. Nearly 11:00 pm here

This is the code to loop through a list of tab names:

Please Login or Register  to view this content.