vba routine to save

[Colleen45]

I have a workbook (Library Stock Register Ver 4.0) that I need to save as 103 different names
I also have the list of names in a separate workbook (called names)which start in column a1
is there a way we could get VBA to do this laborious task
ie take the first name from "Names" list insert it into the save as dialogue of (Library Stock Register Ver 4.0 workbook), close it go to the next name on the list and do the same thing

Thanks
Colleen

 

[Weaver]

Do you need to open, modify and/or save the stock file, or could we just create copies and rename them?

 

[VoG]

Something like this should work. It assumes that both workbooks are open, the names include the file extension, and you may need to change the bits in red to suit

Sub test()
Dim i As Long
With Workbooks("Library Stock Register Ver 4.0.xlsx")
    For i = 1 To 103
        .SaveCopyAs Filename:=.Path & "\" & Workbooks("Names.xlsx").Sheets("Sheet1").Range("A" & i)
    Next i
End With
End Sub

 

[Colleen45]

Do you need to open, modify and/or save the stock file, or could we just create copies and rename them? 
[code]

No the same file just needs to be saved under  103 names,  and yes there is extensive vba code inside the file

 

[Colleen45]

Something like this should work. It assumes that both workbooks are open, the names include the file extension, and you may need to change the bits in red to suit


Code:
Sub test()
Dim i As Long
With Workbooks("Library Stock Register Ver 4.0.xlsx")
For i = 1 To 103
.SaveCopyAs Filename:=.Path & "\" & Workbooks("Names.xlsx").Sheets("Sheet1").Range("A" & i)
Next i
End With
End Sub


Thanks Peter, can i run this from the work book that has the list of names rather, the stock work book has been protected, and i think it may involve more complex issues if we have to unprotect and then protect again just to run the code, my thinking is that it would be simpler to just to take that stock file and rename, yes both files can be open, but the stock one should remain protected

 

[Weaver]

Put the code in the names.xls and make sure it's in the same folder as the sheet you need to duplicate/rename

Sub dupeFile()
    fldr = ActiveWorkbook.Path
    fname = "Library Stock Register Ver 4.0.xls"
    With ActiveSheet
        For Each c In .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
            FileCopy fldr & "\" & fname, fldr & "\" & c.Value & ".xls"
        Next
    End With
End Sub

Change names to suit. This has the advantage of not requiring the stock register sheet to be opened at all.