Merging Excel Sheets (With a Macro?)

December 27, 2006 11:16 AM

MS Excel help needed. I need to set up some sort of automated merging macro in excel and am having trouble tracking down the best way. The details, and much

I have two spreadsheets - the first is a form with some pretty formatting, and the second is a list of several hundred names. I need to set up a macro or script that will take each name from the list, stick it in the "name" field of the form, and then save the form with a new filename (something like name.xls).

If this were in any kind of plain text format, I'd whip up a perl script, but I have little experience using advanced Excel features. Using the Office 2003 XML format is also not an option, because these have to work with older versions of Excel (Office 2000).

Any solutions or even pointers to appropriate search terms would be much appreciated.

posted by chrisamiller to computers & internet (3 comments total)

Using the list of names spreadsheet (list.xls), something like this should work:

Sub makeabunchofspreadsheets()

For i = 1 to 500 (number of names you have)
Windows("list.xls").Activate
name = range("A1").offset(i).value
Workbooks.Open Filename:="C:\blank form.xls"
Windows("blank form.xls").Activate
Range("B1").value = name (or wherever the name is supposed to go in the spreadsheet)
ActiveWorkbook.SaveAs Filename:="C:\" & name & ".xls"
ActiveWindow.Close
Next i

End Sub
posted by milkrate at 11:36 AM on December 27, 2006

That's almost the answer I need. Running that macro saves copies of the form in correctly renamed files, but the name isn't being inserted into the form correctly.

Instead, the name is being inserted into position B1 of the "list.xls" spreadsheet.

The Windows("blank form.xls").Activate command seems to be working, since that windows pops to the forefront, but the value substitution still occurs in the wrong workbook.

I throw myself at your mercy, oh excel guru :)
posted by chrisamiller at 12:07 PM on December 27, 2006

I figured it out. For those of you who are curious, or run across this thread later, this is the script I used:

Sub makeabunchofspreadsheets()
For i = 1 To 885
Windows("list.xls").Activate
Name = Range("A1").Offset(i).Value
Workbooks.Open Filename:="C:\test.xls"
Windows("test.xls").Activate
Worksheets("2007").Range("A5") = Name
ActiveWorkbook.SaveAs Filename:="C:\asdf\" & Name & ".xls"
ActiveWindow.Close
Next i
End Sub

Thanks milkrate!
posted by chrisamiller at 12:42 PM on December 27, 2006