Create multiple excel documents from rows in a sheet...

[murrayb]

I have a workbook and in it are 2 sheets. The first sheet is a form and the second sheet is a list of user information (First Name, Last Name, Amount, Rate).

On sheet one I can fill in the first records information from sheet 2 with =+Sheet2!B2 for Last Name etc.

My question is I have 2k records on sheet 2 and I want to make a new excel document for each record on sheet 2.

For example I have record one is John Smith so I want to create an excel doc with his information, then record 2 is John Doe so create an excel doc with his information filled in.

Does this make sense? Is it possible?

 

[JLGWhiz]

Run this from the workbook with the list of names. It will create a new workbook for each row of data in sheet 2. It will then name the workbook based on column A and B of the copied row plus the month and year by the SaveAs method. It will then close the workbook and go to the next row in the list until each row has been done.

Code:
Sub rosa()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
Set sh = ThisWorkbook.Sheets(2) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
For Each c In rng
Set wb = Workbooks.Add
c.EntireRow.Copy wb.Sheets(1).Range("A2")
On Error Resume Next
wb.SaveAs c & c.Offset(0, 1) & Format(Date, "mmm-yy") & ".xlsx" 'User needs to define this
On Error GoTo 0
wb.Close
Next
End Sub
Code:

 

[murrayb]

So I tried this and does part of it.

When I run the macro it creates a new workbook and labels it with the info from Sheet2 for each person which is awesome.

But if I look at the file, it has the 4 fields I need to populate in my form on sheet1 of the workbook but they are just in a single row of the new workbook and the actual form I need is not there.

So get a workbook with something like:

John Doe 1 4

Then another workbook with:

John Smith 1 5

But that data isn't in the form that I am trying to populate on sheet1 of the same workbook where sheet2 data is located.

Sorry if I am not making sense.

 

[tigeravatar]

Cross posted at Create multiple excel documents from rows in a sheet...

 

[murrayb]

Did not put a cross link in, I apologize.

Cross-link: Create multiple excel documents from rows in a sheet...