I need a macro or vba code to copy an Excel worksheet many times and save each ws.
I have an Excel worksheet called ModelV1.xlsm. On this worksheet, I have a tab called CompanyInformation and a tab called Lookup. On the Lookup tab, I have company names starting on celll A2 to A367.
What I need:
1) I want to copy the first company name on the lookup tab to CompanyInformation cell P15 (would it help if I give the companies on the lookup tab a name range?)
2) Save ModelV1.xlsm with the first name on my lookup tab in cell A2 to path C:\My Documents
3) Perform items 1 and 2 for all the companies on my lookup tab
Thank you.
conernesto
What I need:
1) I want to copy the first company name on the lookup tab to CompanyInformation cell P15 (would it help if I give the companies on the lookup tab a name range?)
2) Save ModelV1.xlsm with the first name on my lookup tab in cell A2 to path C:\My Documents
3) Perform items 1 and 2 for all the companies on my lookup tab
Thank you.
conernesto
This should do it:
Sub test()
Set WshShell = CreateObject("WScript.Shell")
strdocuments = WshShell.SpecialFolders("MyDocuments")
Set WshShell = Nothing
Dim Name As String
Application.ScreenUpdating = False
totalrows = 367
For i = 2 To totalrows
Name = ActiveWorkbook.Sheets("Lookup").Cells(i, 1).Value
ActiveWorkbook.Sheets("CompanyInformation").Cells(15, 16).Value = Name
ActiveWorkbook.SaveAs strdocuments & "\" & Name
Next
Application.ScreenUpdating = True
End Sub
Hi,
The worksheets were created successfully. Can you modify the code so that the files are saved in the path "C:\New Templates".
Can you also modify the code so that the name of each company on the look up list is copied to CompanyInformation cell P15 and then save the worksheet with this name? this is item number one on my request.
Thank you.
The worksheets were created successfully. Can you modify the code so that the files are saved in the path "C:\New Templates".
Can you also modify the code so that the name of each company on the look up list is copied to CompanyInformation cell P15 and then save the worksheet with this name? this is item number one on my request.
Thank you.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
The name was copied to CompanyInformation cell P15 ( missed this). If you can, modify the code so that the worksheets are saved "C:\New Templates" that would be good. Otherwise I will accept your first GREAT ANSWER.
Thank you.
Conernesto
Thank you.
Conernesto
Sub test()
strdocuments = "C:\New Templates"
Dim Name As String
Application.ScreenUpdating = False
totalrows = 367
For i = 2 To totalrows
Name = ActiveWorkbook.Sheets("Lookup").Cells(i, 1).Value
ActiveWorkbook.Sheets("CompanyInformation").Cells(15, 16).Value = Name
ActiveWorkbook.SaveAs strdocuments & "\" & Name
Next
Application.ScreenUpdating = True
End Sub
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.