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:
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?
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?
Last edited:
