Excel macro, cell contents file name, looping?

[Bien1986]

Hello,

I’m not certain how to word this, but here is my attempt:

I have a template in an excel worksheet that needs to be saved using a thousand plus plan names which are listed in another excel sheet under column c.

basically i want to run a macro that will pull the name out of column c (on the list worksheet) and repeatedly save the template file using the plan names listed in column c on the (list worksheet). So once all is complete, there will be a thousand plus files saved in a folder that will all have the template on them.

any advice on how to write a code to do that? (I’m using the developer tab/vba in excel)

-I’m very new to creating macros, so far i've only recorded macros, never written. (i've tweaked per book instructions and I’m still learning language etc). I think I need to add something called a loop to the macro text.

 

[Bill Rockenbach]

I assumed your list of file names is in a separate workbook from the template workbook.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Create a Named Range for the list of names Call it “myNameList”.<o></o>
<o></o>
Copy this code to the sheet code in the workbook that has the list of names.<o></o>
<o></o>
Open your template. And run the code. Suggest using a short list of names for testing..

Option Explicit
Sub CreateWorkbooks()
    Dim ListItem As Range
    Dim sFile As String
    Dim sPriorFile As String
    Dim bFirst As Boolean
    bFirst = True
    For Each ListItem In Range("myNameList")
        sFile = ListItem & ".xls"
        If bFirst Then
            bFirst = False
            Workbooks("myTemplate.xls").SaveAs FileName:="C:\" & sFile
        Else
            Workbooks(sPriorFile).SaveAs FileName:="C:\" & sFile
        End If
        sPriorFile = sFile
    Next ListItem
End Sub

 

[Bien1986]

thanks bill,

I tweaked it to show the name of my list, and i also put the file path the macro needs to follow but i'm getting a vba error 400.

i did an error tap and it reads, "label not defined" ?

 

[Blade Hunter]

On a side note, make sure you don't have any restricted chars in the list, a : would throw a spanner in the works.

 

[Bill Rockenbach]

"label not defined" I had no lables in the code I posted. Did you add any lables say for error checking?

Copy and post your code so I can take a look at it.

 

[Bien1986]

here is what i have, i'm not certain if i need to insert the path to "where" the folders will be saved. (i'm doing practice test with files on my pc before i attemp with shared drive folders)

i entered error tap to see why 400 prompt was occuring, it highlights the "sub createworkbooks()" section of the macro

here is the path for where i'm storing info
C:\Users\Valarie\Documents\1 2010 bills folder

see code below. thanks so much for your help.

Option Explicit
Sub CreateWorkbooks()
Dim ListItem As Range
Dim sFile As String
Dim sPriorFile As String
Dim bFirst As Boolean
On Error GoTo Errorcatch
End Sub
bFirst = True
For Each ListItem In Range("myNamelist")
sFile = ListItem & ".xls"
If bFirst Then
bFirst = False
Workbooks("template.xls").SaveAs Filename:="C:\" & sFile
Else
Workbooks(sPriorFile).SaveAs Filename:="C:\" & sFile
End If
sPriorFile = sFile
Next ListItem
End Sub

 

[Blade Hunter]

This is your problem:

On Error GoTo Errorcatch


You have no Errorcatch label

Try putting this after your last End If (including the : )

ErrorCatch:

Note, there may be a better place for the label but I have not really looked through the code thouraghly.

Edit: On second thoughts for debugging just comment the on error line out, this way when it errors you will be given the option to debug and you can see what the problem is (Which I am still pretty sure is invalid chars)

 

[Bill Rockenbach]

YEs, you need teh full path

If bFirst Then
bFirst = False
Workbooks("template.xls").SaveAs Filename:="C:\Users\Valarie\Documents\1 2010 bills\" & sFile
Else
Workbooks(sPriorFile).SaveAs Filename:="C:\Users\Valarie\Documents\1 2010 bills\" & sFile
End If

 

[Bien1986]

Thanks everyone