Macro to prompt user for file name and save in location

[Fungijus]

Hi folks - looking for a bit of assistance with this one...

We have a requirement to print a sheet of bar code labels and attach to a pallet of stock before shipping. I have the bar code scanner and this inputs data into an Excel spreadsheet. I have developed a bit of code which will remove duplicate lines, select a range of cells, and print the workbook, which works OK.

I want to have the macro prompt the user to enter a file reference number (6 digit numeric),use this number as the file name of the workbook and save a copy of the file using this number in a network location (leaving the original file name and location as the working document).

Here's what I have:

Please Login or Register  to view this content.

This works to a degree, but doesn't use the entered number as the file name, and also saves the original working document with the new name and new location.

I have attached the file also if this helps.
Thanks in advance as always,
J.

[Steffen Thomsen]

Hi

Change this

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[Steffen Thomsen]

You can replace your whole code with this

Please Login or Register  to view this content.

[Fungijus]

Thanks very much for your quick response Steffen.

This works great and produces exactly what I need in terms of a printout, and saves the file (although prompts the user to save as a macro-free workbook, which is OK).

The only problem that remains now is that if the user needs to continue use the sheet (after having printed and cleared the bar codes for instance), it is saved under the new Dimsheetnumber file name.

Is there a way to save a copy of the file under the Dimsheetnumber, and to continue to work in the original file?

Thanks again,
J.

[Steffen Thomsen]

Then change this line

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[Fungijus]

Then change this line
To

Please Login or Register  to view this content.

Thanks again for your help Steffen (and apolgies for the delayed response...).

I have tried as you suggested however I got a Compile Error: Named argument not found.

Doing a bit of reading around, this seems that it could be due to the fact that it is not possible to save a copy of a workbook using SaveCopyAs with a different file extension? I have tried changing the code to save as .xlsm with a FileFormat:=52 (IE same as the souce document) but this doesn't seem to work either.

Any thoughts on this one?

Thanks again,
J.

[Steffen Thomsen]

Then try without specifying the fileformat

Please Login or Register  to view this content.

[Fungijus]

Then try without specifying the fileformat

Please Login or Register  to view this content.

Nice one - this works a treat (actually I had to change the extension to .xlsm in order to avoid a conflict on opening, but apart from that it's great).

Thanks again for your help - I shall put the sheet into operation tomorrow!

Cheers,
J.

[Steffen Thomsen]

Glad i could help!