VBA to create multiple blank workbooks from a list of names in a column

richardcarter

Board Regular
Joined
Dec 10, 2003
Messages
77
I have a list of names in Column A of a workbook (the length of the list will vary) and would like to create a macro which will automatically create a blank workbook for each name in the list.

For example,
A1: Jane
A2: Fred
A3: Harry

I would like to end up with 3 workbooks named Jane.xls, Fred.xls and Harry.xls in a folder in C:\My Documents.

Many thanks in advance to those who can help with what I think should be a simple macro.. but proving impossible to find anywhere!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Rich (BB code):
Sub MakeSheetsFromList()
Dim col As New Collection
dim I as integer
dim vName

On Error Resume Next
Range("A1").Select
While ActiveCell.Value <> ""
    col.Add ActiveCell.Value
    ActiveCell.Offset(1, 0).Select  'next row
Wend

For i = 1 To col.Count
   vName = "C:\My Documents\" &  col(i)
       Workbooks.Add
       ActiveWorkbook.SaveAs vName
       ActiveWorkbook.Close False
Next
Set col = Nothing
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top