Save a Workbook based on a list (Excel 2002)

Topic

New Reply

I need to save a single customized workbook using a list of 500 names. Is there a way to automate this? Thanks!!

Reply | Quote

Replies

What exactly do you mean by “save … using a list of 500 names”? Do you want to perform Save As 500 times? Or …?
It would be helpful if you could provide some details (where is the list, etc.)

Reply | Quote

That was quick! Yes Save As 500 times and the list maybe from another worksheet or a text file whichever is simpler.

Reply | Quote

You could use a macro like the following. Change the path (folder) where the files should be saved, and change the names of the workbook to be copied and the workbook/worksheet containing the list of names. I have assumed that the list starts in cell A2.

Sub Save500Times()
‘ Modify as needed, keep trailing backslash
Const strPath = “C:Excel”

Dim wbkList As Workbook
Dim wbkOrig As Workbook
Dim wshList As Worksheet
Dim lngMaxRow As Long
Dim i As Long

On Error GoTo ErrHandler

Set wbkList = Workbooks(“List.xls”)
Set wshList = wbkList.Worksheets(“List”)
lngMaxRow = wshList.Range(“A65536”).End(xlUp).Row

Set wbkOrig = Workbooks(“Data.xls”)

For i = 2 To lngMaxRow
wbkOrig.SaveCopyAs strPath & wshList.Range(“A” & i) & “.xls”
Next i

ExitHandler:
Set wshList = Nothing
Set wbkList = Nothing
Set wbkOrig = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Hans thanks again the code does the job well. If it is not to much to ask can we push this a little further. What I mean is before the Save As, is it possible to plug the name into Cell A2 (of the copied workbook) first and then issue the Save As routine?

Regards
jolas

Reply | Quote

Try this:

For i = 2 To lngMaxRow
wbkOrig.Worksheets(“MySheet”).Range(“A2”) = wshList.Range(“A” & i)
wbkOrig.SaveCopyAs strPath & wshList.Range(“A” & i) & “.xls”
Next i

Replace MySheet with the correct sheet name.

Reply | Quote

Another excellent coding Hans . Truly appreciate the help!!!

Reply | Quote