Copy and rename file from list

Wil Moosa

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
893
I have one file; Mother.xls

Mother.xls has on sheet(6) a list with names of all the employees. I want to create as many copies of Mother.xls as found on the list on sheet(6) each file carrying the name as mentioned on the list.

The following code does the trick for me:
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Code: 
Sub CopyToEachName()
    Dim cell As Range
     ThisWorkbook.Save
    For Each cell In Worksheets("Sheet6").Range("A2", _
        Worksheets("Sheet6").Range("A2").End(xlDown))
        ThisWorkbook.SaveCopyAs _
            ThisWorkbook.Path & Application.PathSeparator & cell.Value & ".xls"
    Next cell
End Sub
 
Upvote 0
I already solved your problem. This method is better than SaveAs. It replaces the files if they exist already.
 
Upvote 0
I do see the potential of your code... and it did work. Any practical suggestion for deleting sheet6 in the copies?
 
Last edited:
Upvote 0
A For .. Next loop in a personal macro keeps active after you save the file with another name.

First store the name of the list in a variable before you delete sheet(6). SaveAs xxx.xls, close xxx.xls and re-open the original file.
 
Upvote 0
Code: 
Sub Sheet6CopyToEachName()
    Dim sWB0 As String
    Dim a, v
    
    ThisWorkbook.Save
    sWB0 = ThisWorkbook.FullName
     
     a = Worksheets("Sheet6").Range("A2", _
        Worksheets("Sheet6").Range("A2").End(xlDown))
        
    ThisWorkbook.Save
    Application.DisplayAlerts = False
    Worksheets("Sheet6").Delete
    Application.DisplayAlerts = True
    
    For Each v In a
        ThisWorkbook.SaveCopyAs _
            ThisWorkbook.Path & Application.PathSeparator & v & ".xls"
    Next v
    
    Application.DisplayAlerts = False
    Workbooks.Open sWB0
    Application.DisplayAlerts = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Copy a sheet which is a template based on a drop down list and rename each copied tab with each value in the drop down list
2 3
Replies
21
Views
1K
dmt32
D
S
  • Question Question
VBA Code that extracts multiple tabs at a time based on lookup tab
2
Replies
10
Views
347
NoSparks
N
L
  • Question Question
Moving and renaming multiple workbooks from a master workbook.
Replies
1
Views
229
Luke1690
L
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
391
Amo840812
A
R
  • Question Question
How to copy a range of data if two cells match
Replies
2
Views
238
rockychelsea
R

Forum statistics

Threads
1,222,697
Messages
6,167,702
Members
452,132
Latest member
Steve T

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top