save as using cell data but dont replace file

C

cosmo37

using this save as
ChDir "S:\docs here"
ActiveWorkbook.SaveAs Filename:="S:\docs here\" & _
Range("L8").Value & ".xls", FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
this works until i need a second file with same L8 data but dont want to
over write the first...
when i click NO to replace, i get runtime error 1004, method saveas of
object workbook failed
*** here is what i want, when i click NO i want the option to edit the file
name***
(would love it if it auto changed name to (L8)_1 if that is already there
then (L8)_2 and so on)

am i asking too much... can anyone help... thanks in advance
 
J

JLGWhiz

If you put the save as in a loop for a known number of times that you will
save the file, it might be practical to do what you want. But, by the
description that you have posted, it appears that the number of times that
you might click the button is somewhat arbitrary, or at least is not based on
a consistent criteria that occurs at regular intervals. So, to do what you
want to do, you would need to be able to define a criteria that will exist at
the time you decide to do the saveas, so that you can use an If...Then logic
evaluation to determine whether or not to add a suffix to Range("L8").Value.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top