Macro copy/save help

[delighted]

I am trying to create a macro within a worksheet to create a copy of the active sheet (named 'Invoices') and then save it to a specific folder on the desktop (named 'Saved Invoices') The macro works all the way up to the save point. From there I get a Run-time error of 424. Is there someone that could please help me finish this out? I would greatly appreciate any help provided. This is the macro as is currently appears:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I assume you are trying to save a Workbook containing a single sheet. In which case something like

Please Login or Register  to view this content.

It appears Excel does not like "/" date separators in file names hence the Format(Date,"dd mm yyyy") bit of code.

Regards

[delighted]

I still receive a 1004 run-time error at the ' ActiveWorkbook.SaveAs Filename:=strPath & strName, FileFormat:=51' line. This line is just not working and I don't understand why. Thank you for your help.

[delighted]

BTW, you are correct in what I am trying to accomplish.

[Richard Buttrey]

Hi,

In that case please upload your workbook so that we can see the request in context.

Regards

[delighted]

Hi,

In that case please upload your workbook so that we can see the request in context.

Regards

How do I upload my workbook to here?

Thanks

[delighted]

I am attaching the workbook that I have been working on (I hope). The current macro that I am working on is the 'Save Invoice' macro on the sheet named 'Invoice'. Within the VBA there are several 'Save Invoice' macros, each with a slightly different name, as I have been progressing through working on the invoice.

Thank you so, so much for any help you can provide.

[Richard Buttrey]

You have not used the macro I gave you in post number 2.
Please do this first and then if you still have a problem post back.

[delighted]

My apologies. I apparently didn't save the file after I had inserted your macro when I saw it didn't work. I receive a Run-time error of '1004'. I saved it in there this time with the name 'saveinvoiceRB()'.

Thank you for your assistance.

[Richard Buttrey]

Hi,

You had still not copied exactly the code I gave you.

Specifically you continue to use

Please Login or Register  to view this content.

which syntax is unknown to VBA.

Other problems.

1. Using the DATE in the filename is fine except that the default date inculdes "/" characters, and for some reason Windows does not like these characters in file names. You need to change the "/" to a "_" or some other character which is allowed. Hence the Replace() code in the code below.

2. You use ActiveSheet.Copy, which is fine and which creates a new workbook, but then in the next line you use a 'With ActiveSheet.Copy' which simply creates another workbook. You don't need this.

The corrected code is below. Please be sure to copy this carefully.

Please Login or Register  to view this content.

[delighted]

Sir,
I have gone back and taken out all of the other macros that I was working on to try to resolve this issue so there wouldn't be any confusion (on my part). I then copied and verified your code, and ran it. I am still receiving Run-time error '1004'. I have attached the workbook so you can see that I have done it correctly(hopefully). The message boxes do provide what the accurate file path and name should be once it saves. The debugger continually goes to the SaveAs line.

Thank you for your patience and understanding in helping with this. I really do appreciate it.

[delighted]

I guess will stop this quest for assistance as noone seems to be able to help me through this last bit. I have been trying to figure this out for 2 months and it just isn't going to work. Thanks to all those who tried.