Macro Stopped Working for No Discernable Reason

[liquidmettle]

Hi Everyone!

A while back I received help with the macro in this thread: https://www.excelforum.com/excel-pro...ml#post5256672

I went to run it for the new year and the debug tool is getting caught up on the following:

Please Login or Register  to view this content.

I cannot for the life of me determine why this has become an issue. I have not changed the folder locations or file names as far as the macro should be concerned.

The folder structure is as follows: Desktop>Department Dailies>Daily Dash Copies>Base Template

Where the "Names for Dailies" sheet with the macro is in the "Daily Dash Copies" subfolder, and the template it should be replicating is in the "Base Template" subfolder.

Anything I might be missing?

EDIT: Using Excel 2016

Thanks for your consideration.

[ByteMarks]

If it's just entering debug without any error you could try pressing Ctr+Break a few times next time it does it, then continue running.
I get this phantom break point issue a fair bit and that always fixes it for me.

[liquidmettle]

Hi Byte,

So took me a moment of searching to realize that the pause key is the break key on my keyboard, but it didn't seem to do anything when I mashed it with ctrl, and ctrl+fn. I tried the onscreen keyboard as well (and that seemed to map my pause key to scroll lock even with the scroll lock next door). Clicking the onscreen keyboard pause didn't seem to have an effect.

I double checked and the error I was getting was Run-time error '-2147352571 (80020005) The item with the specified name wasn't found.

I'm thinking that means the part of the code that is throwing fits is "Filepath & Workbooks("Names for Dailies")" since the N4D workbook is not in the Base Template folder but rather one directory up. But this still confuses the hell out of me since this code has worked reliably for years (and I only run it 1-2 times a year).

The "myfile" finds the proper base template to copy in the directory when I hover over it, so I'm just stumped.

I also tried making a second filepath named filepath 2 that stopped at the Daily Dash Copies folder level thinking maybe it wasn't finding the N4D workbook but no dice.

[liquidmettle]

Made a little progress.

Changed the line to read from:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

The macro proceeds, copies the template file and renames its with the first entry in the "Names for Dailies" column A.

It gets tripped up again when I try to have it add other values from the Names for Dailies workbook (where the code is), to the copied file.

The problem is definitely the vba knowing to reference the open workbook I started with. I tried tinkering with activeworkbook but definitely don't remember the proper syntax.


Full Code:

Please Login or Register  to view this content.

[ByteMarks]

What if you store the source worksheet in a variable?

Please Login or Register  to view this content.

[liquidmettle]

Hi Byte,

First, your code worked perfectly!

Second, I had stumbled on an alternate solution just about when you were posting yours, and that was to add the extension in the sheet name. I ran your code as well to verify and both worked great (yours is much cleaner though and will likely be what I keep).

Thank you for your help!!


Alternate code (has one or 2 other tweaks as well):

Please Login or Register  to view this content.