Copy and rename file from list

[Wil Moosa]

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:

Sub Aanmaken_peroonlijk_kwartaaloverzicht_Batch()
Sheets(6).Select
Range("A1").Select

Do
Application.Run "Aanmaken_peroonlijk_kwartaaloverzicht"
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = Empty
Range("a1").Select

MsgBox " Alle bestanden zijn aangemaakt." _
, vbInformation, " Groene Kruis Thuiszorg"
End Sub

Sub Aanmaken_persoonlijk_kwartaaloverzicht()
ThisWorkbook.SaveAs ("C:\Documents and Settings\Wil Maassen\Bureaublad\Kwartaaloverzichten\" & ActiveCell.Value & ".xls")
End Sub

Two things I can not figure out.
*When an error pops up (File already exists, Do you want to skip, Yes.... ) the loop comes to a hold. Can I prevent this? How?
*In every copy the sheet(6) needs to be deleted. Where in the loop I need to add what code to achieve this?

 

[WinteE]

Hi,

To prevent the error you'll have to use an if statement to delete the file before the ...SaveAs, such as :

If File Exists "C:\Documents and Settings\Wil Maassen\Bureaublad\Kwartaaloverzichten\" & ActiveCell.Value & ".xls" Then
Kill "C:\Documents and Settings\Wil Maassen\Bureaublad\Kwartaaloverzichten\" & ActiveCell.Value & ".xls"
EndIf

...SaveAs


To achieve the deletion of the sheet where the list with file names is in you'll have to use the macro as a personal macro.

Number the list-items from 1 to ? and loop through the list by number.

Success,
Erik

 

[Wil Moosa]

Did not figure it out yet. Got two threads with the same content mixed up too. http://www.mrexcel.com/forum/showthread.php?p=1807740&posted=1#post1807740 Please stick to this thread.