Iterating Macro 1004 error

[Paulb1985]

Hello all,

I've got a macro that copy-pastes project data from two different sheets into a new sheet, saves this third sheet as a new file, and loops through that same process with the next batch of data (i.e. the next project).
The macro works fine...for 5 files, then it gives a 1004 warning about protected workbooks (see screenshot).

This surprised me because a) the macro itself sets a password on the newly created file AFTER doing all the copy-pasting, and b) why would the macro run fine for the first 5 files, and then suddenly stop?

Before posting the entire code, I wondered if there's a generic answer / thing I might have overlooked that causes a macro to run into this error midway through the iteration.

Any thoughts are much appreciated.

Best,

Paul


EDIT:
It may be useful to know that the debugger points me to the line
wbkOut.Close SaveChanges:=True

 

[Joe4]

Welcome to the Board!

Does the offending file have any VBA code in it, especially any code in the "ThisWorkbook" module?
Do any of the files have any protection on them from the beginning?

 

[Paulb1985]

Welcome to the Board!

Does the offending file have any VBA code in it, especially any code in the "ThisWorkbook" module?
Do any of the files have any protection on them from the beginning?

Hi Joe4,

Good questions, thanks for your quick reply. I do actually import 2 sets of VBA code (.bas files) into the new file. They do not run during the export and saving though; they are only available to the end user of the new files.

The files do not have protection from the beginning - I copy data from unprotected worksheets into new worksheets of a new file.

 

[Joe4]

But is there any VBA code in the "ThisWorkbook" module of the offending file?

 

[Paulb1985]

But is there any VBA code in the "ThisWorkbook" module of the offending file?

Yes, there is a VBA module in the original file. It’s function is to create the exported file. The output file also contains VBA code, namely the 2 .Bas files / macro’s that are imported into the newly created files. Any idea how this could run well for the first 4 files exported, but then generate the error? Thanks again for your thoughts!

 

[Joe4]

Yes, there is a VBA module in the original file. It’s function is to create the exported file. The output file also contains VBA code, namely the 2 .Bas files / macro’s that are imported into the newly created files. Any idea how this could run well for the first 4 files exported, but then generate the error? Thanks again for your thoughts!

No, that is not what I am asking.

In VBA, there are three main different modules that VBA code can reside in:
- "ThisWorkBook" module - this is where you typically put automated VBA code that runs upon things like opening the file, closing the file, before saving, before printing, etc.
- "Sheet" modules - this is where you typically put automated VBA code that runs on things like changing specific ranges on your sheet, selecting specific ranges on your sheet, code that runs whenever a calculation occurs, etc
- "General" modules - these are VBA modules that you add yourself, that contains VBA code that is run manually, either from the macro menu, if called from keyboard shortcuts, or buttons you create, etc.

I want to focus on any automated VBA code that may be interfering with what you are trying to do. Hence, I want to know if any VBA code exists SPECIFICALLY in the "ThisWorkbook" module of the workbook that you are having issues with. I am not concerned with any other VBA code, at the moment.

 

[Paulb1985]

Dear Joe4,

Thanks for that clarification - I was not aware that the macro could be saved in either ThisWorkbook, Sheet, or General.
I suspect it is saved in General. When I open the file and go to Developer>Macros, select the relevant macro, and click Edit, the pop-up window with the code shows that in the file 20220107 MS Transfer, in Module "ExportSheets", there's a code called "ProcessAllDropDownItems" in "(General)" (see screenshot). Is my understanding here correct?

 

[Joe4]

It is important to understand the different kind of modules, and what they mean to your code.

There are things called "event procedure VBA code". Event procedures are events that automatically trigger VBA code to run based on some event happening. They appear in one of the pre-defined modules associated with that particular workbook, specifically one of the Sheet modules or the "ThisWorkbook" module.

I want to check to see if there is any of this automated code running which may be interfering with your processes.
So all I want you to do is double-click on the "ThisWorkbook" module in VBA Project Explorer, and let us know if there is any VBA code in there. If so, please post it here!

 

[Paulb1985]

Dear Joe4,

Thanks. When I open the ThisWorkbook location, I only see some code when selecting "Workbook":

Does this come close to a suspect activity that might be the troublemaker?

Many thanks in advance,

Paul

 

[Joe4]

Dear Joe4,

Thanks. When I open the ThisWorkbook location, I only see some code when selecting "Workbook":
View attachment 54687

Does this come close to a suspect activity that might be the troublemaker?

Many thanks in advance,

Paul

Hmm, no there is nothing there that would interfere with anything.

Are you sure that the offending sheet (the one giving you problems) doesn't already have protection on it from the start?