List and Sheet Create Macro

M

Michael Santoro

I am new to macros, but I am trying to build a list that
will create a new worksheet for each line, naming the
sheet with the 1st col's data. Then I'd like to have a
lookup pull data from the list to the sheet to populate
the data from the other columns in the list. Any ideas?
 
N

Norman Jones

Hi Michael,

I am not sure that I have interpreted properly your requirements, but my
understanding is that you have a list is named (say) MyList in the active
workbook. You wish to produce a new single-sheet workbook for each row in
the list. Each new workbook is to be saved with the name entered in the
first cell of the row. The cells in the first row of the new workbook are
to be linked to the source row , so that any changes in the master workbook
are reflected in the subsidiary workbooks.

If this interpretation is correct, try:

Sub Tester()
Dim WB As Workbook
Dim rng As Range, cell As Range

Application.ScreenUpdating = False
Set WB = ActiveWorkbook
Set rng = Range("List")

For Each cell In rng.Columns(1).Cells
rng.Parent.Copy
ActiveSheet.Cells.ClearContents

ActiveSheet.Cells(1, 1).Resize(1, rng.Columns.Count) _
.Formula = "=" & cell.Address(0, 0, external:=True)
ActiveWorkbook.Close True, cell.Value
Next cell
Application.ScreenUpdating = True
End Sub
 
M

Michael Santoro

Actually, I was looking to have the sheets add to the
workbook with the list...thanks for this...how would you
change it?
 
N

Norman Jones

Hi Michael,

Perhaps my powers of comprehension are waning, but I find it difficult to
reconcile
your original objective:

{unexpurgated]
I am new to macros, but I am trying to build a list that
will create a new worksheet for each line, naming the
sheet with the 1st col's data. Then I'd like to have a
lookup pull data from the list to the sheet to populate
the data from the other columns in the list. Any ideas?
Click to expand...


with your additional detail:

Michael Santoro said:
Actually, I was looking to have the sheets add to the
workbook with the list...thanks for this...how would you
change it?
Click to expand...

Perhaps you could proviide an extended explanation.
 
Top