We help IT Professionals succeed at work.

I need a macro or vba code to copy an Excel worksheet many times and save each ws.

Conernesto
Conernesto asked
on
368 Views
Last Modified: 2014-05-05
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
Comment
Watch Question

KimputerIT Manager
CERTIFIED EXPERT

Commented:
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

Open in new window

Author

Commented:
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.
IT Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
KimputerIT Manager
CERTIFIED EXPERT

Commented:
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

Open in new window

Author

Commented:
Hi,

I want to understand some of the meanings on the code you provided. What does "strdocuments" below represent/mean?

strdocuments = "C:\New Templates"

What does the following code mean?

Application.ScreenUpdating = False
KimputerIT Manager
CERTIFIED EXPERT

Commented:
strdocuments Is a variable, in this case, the folder you wanted. Makes it easier for you to change the destination folder in one go.

Screen updating set to false is to make the script run faster especially when looping through cells and updating them.
Unlock the solution to this question.
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.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.