Save active workbook Filename glitch

[Frankie_The_Flyer]

Hi again!

My code copies a worksheet into a new workbook, names the sheet, deletes the "Sheet1" that comes with the new workbook and is then meant to save the workbook in a folder that has previously been checked as being real, under a filename made up form data in the worksheet.

All is good until it comes to the filename the sheet is saved as. If I step through the code the line

Please Login or Register  to view this content.

shows up with the full path and file name. The Message Box also comes up with the correct information. HOWEVER, The filename the the new workbook saves under is "FALSE.xlsm"

Here's the relevant bit of code

Please Login or Register  to view this content.

Any ideas why this doesn't work please??

Thanks

Frankie

[HaHoBe]

Hi Frankie,

maybe change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Besides: the relevant part would be where the variables get their values, and I would think about renaming most of the variables you use as they are part of the VBA Keywords.

Ciao,
Holger

[Sintek]

Your code does not specify what value of Filename is...
Should the red snippet not be CName

Please Login or Register  to view this content.

Perhaps upload a sample file and explain in detail what it is you are trying to achieve...

[HaHoBe]

Hi Frankie,

first of all I wonder why you would need to delete sheets from a workbook you copied a worksheet to. It´s only necessary if you add a workbook first and then copy opver the sheeet. You could avoid delting sheets by just using

Please Login or Register  to view this content.

which would copy the sheet over into a newly created workbook with no other sheets.

In your snuplet you use wb as well as ActiveWorkbook. I suppose it´s the same workbook rhroughout and you should be consoistent in the code to use for a certain workbook.

Last the question why the code line returns False for the workbook name. You compare the filename to another string which also contains the filename plus more information. So no match would ever be found. And if a match is Found the filename for teh workbook would be True...

The proper syntax for the SaveAs Command would be

Please Login or Register  to view this content.

and it should be used as

Please Login or Register  to view this content.

HTH,
Holger

[Frankie_The_Flyer]

Hi sintec

Thanks for the input.

CName is the name of the sheet while Filename is the name for the workbook being saved. Company Name and Invoice number for the sheet and "Invoice For" Company Name and Invoice Number along with file extension for the filename.
(Discussed with the end user who wants it this way!!)

Cheers

Frankie

[Frankie_The_Flyer]

Hi HaHoBe

Thanks for your input too.
I loaded your code and changed the Const cstrPATH, Folder, SubFolder to the names I want but when it comes to the file name

Please Login or Register  to view this content.

it throws up "constant expression required" and highlights the ".Value". If I remove .Value it just highlights the ".Range"

The FileName is variable dependent of info in cells C3 and J9 so how do I write this for the Const cstrFileName please?

Frankie

[HaHoBe]

Hi Frankie,

imy sample was for demonstration only, so I used Constants. If you rely on variables like information from ranges you should use

Please Login or Register  to view this content.

and change the variable in the line for saving as well.

Ciao,
Holger

[Sintek]

Frankie...I suggest your sample file upload and explain your end result requirement...You know that this has always proven to render results...
i.e. I want this file to be saved in this SubFolder of this Folder named from this range etc etc etc ...

[romperstomper]

Your syntax is wrong - you're missing a colon:

Please Login or Register  to view this content.

[Frankie_The_Flyer]

OK. Here's a sanatised version of the workbook.

Everything appears to work aside from saving the Invoice under the company name and invoice number in the folder "REC" SubFolder "REC Invoices".

Thanks for the assistance

Frankie

[HaHoBe]

Hi FRankie,

AFAIK the character # should be avoided for filenames.

I would have expected most of the procedures in a standard module as by now the codes are copied to the new workbook due to the fact that they are placed behind the sheet.

Code for TrackInvoice:

Please Login or Register  to view this content.

Code for SaveInvoice where I changed # to No.:

Please Login or Register  to view this content.

Ciao,
Holger

[HaHoBe]

Hi Frankie,

if the codes aren´t needed in the copy you could consider to save as a normal workbook with the extension xlsx (FiuleFormat woul be 51) which would leave you with a macro free copy.

As there are problems reported with that way you should take the road with a standard moule for the codes.

Ciao,
Holger

[Frankie_The_Flyer]

Good Stuff HaHoBe!! Vielen Dank!

Frankie

[HaHoBe]

Hi Frankie,

thanks for the fededback. I attach a copy of your workbook where all procedures have been put into modules (where possible) and those begind the sheet made to only work in the workbook due to comparing the workbook name to constants.

Maybe have a look at it.

Ciao,
Holger

[Frankie_The_Flyer]

Thanks HaHoBe. (I was going to do this but got carried away with building the workbook! :-))