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!
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,821
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:

Forum statistics

Threads
1,086,096
Messages
5,387,807
Members
402,080
Latest member
scott2020

Some videos you may like

This Week's Hot Topics

Top