Oops, forget this
& ".xls"


ActiveWorkbook.SaveCopyAs PathStr & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & i & ".xls"


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:ewzUxJCKGHA.1728@TK2MSFTNGP14.phx.gbl...
> Hi Rob
>
> Test this
>
> With the reference numbers in Sheets("Sheet1").Range("A1:A5")
> it will copy the value in the loop in Sheets("Sheet2").Range("D1")
>
> Then print and save the file with a number in the PathStr folder
>
>
> Sub test()
> Dim PathStr As String
> Dim cell As Range
> Dim i As Long
>
> PathStr = "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\"
>
> i = 0
>
> With Sheets("Sheet2")
>
> 'loop through the reference numbers In Sheets("Sheet1").Range("A1:A5")
> For Each cell In Sheets("Sheet1").Range("A1:A5")
>
> 'D1 in Sheets("Sheet2") is the lookup value of your vlookup formulas
> .Range("D1").Value = cell.Value
> Application.Calculate
> .PrintOut
> i = i + 1
> ActiveWorkbook.SaveCopyAs PathStr & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & i
> Next cell
>
> End With
>
> End Sub
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "RobMack" <RobMack@discussions.microsoft.com> wrote in message news:EACEF24A-402C-46E5-BCB9-9E24379A4FD2@microsoft.com...
>> Hi Ron,
>>
>> I need to save different files in sequence. The process I have created cuts
>> a reference number from ws 1, pastes into ws 2 that has vlookup formulas to
>> retrieve other information from the reference number pasted, then prints, and
>> then saves each file with different information. This process repeats until
>> there are no more reference numbers.
>>
>> I am having some trouble with the saving. I have listed the code below that
>> I have been trying to develop for "saving as".
>>
>> ChDir "S:\RVI\Documents\BNS References\Audit Memos"
>> ActiveWorkbook.SaveAs Filename:= _
>> "S:\RVI\Documents\BNS References\Audit Memos\BNSTest\B200.xls",
>> FileFormat _
>> :=xlNormal, Password:="", WriteResPassword:="",
>> ReadOnlyRecommended:= _
>> False, CreateBackup:=False
>> fName = ActiveWorkbook.FullName
>> ' remove .xls
>> fName = Left(fName, Len(fName) - 4)
>> i = 0
>> Do
>> i = i + 0.1
>> fName1 = fName & i & ".xls"
>> Loop Until Dir(fName1) = ""
>> ActiveWorkbook.SaveAs fName1
>>
>> I'm not sure if this is of any help, but any assistance would be appreciated.
>>
>> Rob
>>
>>
>> "Ron de Bruin" wrote:
>>
>>> Hi Rob
>>>
>>> You can use the Date/Time in the file name maybe
>>>
>>> Do you want to save the same file a few times with a different name
>>> (Do I understand you correct ?)
>>>
>>> Please explain what you want to do
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>> "RobMack" <RobMack@discussions.microsoft.com> wrote in message news:7F4ED29A-FF86-4AEC-9DF8-D445486BD096@microsoft.com...
>>> > Only problem is that I need an individual name for each file saved. This
>>> > saves into same file name over and over.
>>> >
>>> > "Ron de Bruin" wrote:
>>> >
>>> >> Hi Rob
>>> >>
>>> >> Use this
>>> >>
>>> >> Application.DisplayAlerts = False
>>> >> 'Save code
>>> >> Application.DisplayAlerts = True
>>> >>
>>> >>
>>> >> --
>>> >> Regards Ron de Bruin
>>> >> http://www.rondebruin.nl
>>> >>
>>> >>
>>> >> "RobMack" <RobMack@discussions.microsoft.com> wrote in message news:289E9AFC-3EF9-4B36-BBE1-EBEF8C6273C3@microsoft.com...
>>> >> >I have a procedure whereby files need to be automatically saved, "save as".
>>> >> > How can I code the procedure so that it will rename the file and I don't get
>>> >> > the error message that "file already exists, do you want to replace"?
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>