Avatar of Conernesto
Conernesto
Flag for United States of America asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Kimputer

8/22/2022 - Mon
Kimputer

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

Conernesto

ASKER
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.
ASKER CERTIFIED SOLUTION
Kimputer

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Conernesto

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Kimputer

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

Conernesto

ASKER
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
Kimputer

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.