Link to home
Create AccountLog in
Avatar of Conernesto
ConernestoFlag 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
Avatar of Kimputer
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

Avatar of 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
Avatar of Kimputer
Kimputer

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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

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
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.