save as using cell data but dont replace file
 

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

save as using cell data but dont replace file
 

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.



"cosmo37" wrote:

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