Error emailing a single sheet from a workbook

[rkjudy]

I have an original workbook that I use daily stored in a folder called 'Live'. I also have this same workbook saved in a different folder call 'In Progress' because I am constantly improving and making changes to it. While I'm in the revision mode, I keep it in this folder. Once I've completed my revisions, I move it to the folder where the live workbook is located and replace the old version. Therefore, I have two copies of the workbook at all times. The name of the workbook in both folders is the same (IRQT)

I have a macro in this workbook that will allow you to save a copy of the workbook in a folder called IRQTArchive. The macro will create the folder if it doesn't exist, then store a copy of the workbook using a name in a cell. After that is complete it renames the workbook back to the original name (IRQT).

This macro works perfect in my live version, but when I execute the macro in the 'In Progress' version, I get a Compile error: Sub or Function not Defined when it reads the dirExists command in the first line of code. The code in both sheets is identical. Here are the first few lines of code. The command dirExists is highlited in yellow when it errors out.

Please Login or Register  to view this content.

Any suggestions?

[Leith Ross]

Hello rkjudy,

The Dir command can take an additional argument called attibute. If you leave this blank, VBA assumes you are looking for a file name. To be sure that VBA understands this is a folder name and not a file name use the vbDirectory constant after the file name in the statement.

Please Login or Register  to view this content.

[rkjudy]

Should I leave the 'Not' in the 'If' statement (If Not Dir)?

[Leith Ross]

Hello rkjudy,

Oversight on my part. The "Not" should be removed from the "If" statement.

Please Login or Register  to view this content.

[rkjudy]

After applying your fix above, I get the same error. Any other ideas?

[rkjudy]

BTW, I have this exact same code running in a previous version of the workbook and it runs perfect. I just don't understand the difference and why it will work in one workbook and not the other. Please help.

[Leith Ross]

Hello rkjudy,

Are both workbooks running Windows XP with Office 2003 or something else?

[rkjudy]

Both workbooks are running on the same machine, XP SP3. When I do an 'About Excel' it says Microsoft Excel 2002. The funny thing is that it worked perfect for a long time, then after making some revisions (nothing to do with the SaveACopy macro, just some minor cell value changes), I started getting this error.

[Leith Ross]

Hello rkjudy,

To determine if the computer is at fault, copy the workbook from the computer that is failing to the computer that is running. If the workbook runs correctly then there is something different about the other computer. You said it was upgraded. What was the upgrade?

[rkjudy]

The fact is that the computer is not failing. I have an older version of this workbook in another folder on the same machine with the exact same 'SaveACopy' macro and it works perfect. The only difference in the two versions are a few cell values (ie. from $100 to $110). Nothing pertaining to the the 'SaveACopy' macro has changed from the older version to the newer version. The 'SaveACopy' macro contains exactly the same code in both (the old and new) workbooks.

[rkjudy]

BTW, sorry about the title of this thread. I actually have two threads going and I got confused. The title should have been "Error trying to save a copy of a workbook using VB".

[rkjudy]

I found another source with another option and it fixed my problem. The change that fixed my macro is below.

Please Login or Register  to view this content.