Run time error 1004 - why is the last line of this code causing this?

what Norie said.

In order to troubleshoot, try putting

Please Login or Register  to view this content.

in just before the activeworkbook.saveas command, just so you can check that a valid file path is being generated.

Thank you all,

I have added in the msgbox and it looks like the right file path, but then still comes up with the run time error.

It creates the file with the correct file name, but just stops short of actually saving it.

Sorry about that, rules read and understood, an oversight on my part, brain drain caused by macro poblems!!

Hello All,

I have tried the suggestions above, and tweeked the code to be more specific about the file type (so it is macro enabled), but I get the same error message, run time error 1004 and the last line of code is highlighted.

I'm wondering if the problem is because I am copying the whole sheet with a copy / paste function rather than righting code to duplicate the tab, but then I'm not sure how to do this so will record myself doing it and see what the code looks like!

Thank you for your help with this, I'm quickly running out of time to get this working, eek!

Please Login or Register  to view this content.

Hi,

The values of spath is fixed up until the last folder in the directory, which is named after a cell in the file, which would be something like A563403 (which is cell C4) and then cell B4 is a name, for example Bletchley. So I am telling the macro to save the file in the folder F:\Landfill............\A563403 Bletchley.

The sfilename is then also based off of cells in the file and would be the A563403 and then Scenario2 (from cell B6).

The theory is then that the user can select a cost centre, select a scenario, run the macro which then saves a file is the relevant location. It is always getting stuck at the last line and giving me the same message.

I hope that all makes sense!

Thank you for that!

I ran it and the error message, path not found comes up, so there must be something wrong with my file path. I shall go over it with a fine tooth comb!

I have tried that as well now, it now reverts back to the old error message of run time error 1004. The part it highlights is the ActiveWorkbook.saveas

Please Login or Register  to view this content.

I can save it manually without any problems. I have just recored myself doing it manually, and this is the code I get.

I can sucessfully change the code so that the file path is linked to the cells, but when I do the same with the file name it falls over. I will make the changes again and post what I end up with when it crashes.

Here is the 'clean' code froom my recording:

Please Login or Register  to view this content.

Firstly, thank you to everyone for your suggestions, I feel I am making progress!

Here is the code (from above) after make the File path link to two cell references. The macro still works at this point. When I try and do the same to the file name coding it falls over. So my question is, how do I adjust the file name coding so that the last part of the folder directory is the same as the file path, and the file name links to cell C4 and cell B6 of the INPUT sheet?

Please Login or Register  to view this content.

I have recorded myself saving the file manually and have managed to change the coding so that the file path links to the relevant cells in the file.

At this point the macro works perfectly, but when I try and change the file name coding to work in the same way, it falls over and highlights the entire file name coding. What coding should I write so that the last part of the file directory in the file name coding replicates the file path coding, and then what coding should I use to tell the macro that the file name comes from cell C4 and B6 of the INPUT sheet?

Here is the coding that currently works:

Please Login or Register  to view this content.

Sorry folks, I posted the samething twice above, my pc crashed and I didn't think it posted, so I did it again, only to discover it had posted my response!

This is now solved, here is what I ended up doing:

Please Login or Register  to view this content.

Thank you to everyone for your help!

Try using this instead:

Please Login or Register  to view this content.

Where xlApp is your Excel Application.

It worked for me in a similar problem