Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save a Workbook based on a list (Excel 2002)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save a Workbook based on a list (Excel 2002)

    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.)

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Workbook based on a list (Excel 2002)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save a Workbook based on a list (Excel 2002)

    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

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Workbook based on a list (Excel 2002)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save a Workbook based on a list (Excel 2002)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Tokyo, Japan
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save a Workbook based on a list (Excel 2002)

    Another excellent coding Hans <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> . Truly appreciate the help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •