How to create many worksheets from master, Excel 2010

[Rholly]

I have been searching and searching and cannot find what I need. I am only an intermediate user, so if I have to use a macro or something, please walk me thru it.
I have a master Excel 2010 book with approx 30 columns from A thru AD
And 130 rows. Column A is sorted by last name, with only 26 different names. So many names in Column A are duplicated several times with various info.
ie--6 Miller rows, 4 Smith rows, 3 Thomas rows, etc.
How can I automatically make a separate sheet (like an invoice) in a NEW workbook using info from the master book each time the last name changes? I have a master invoice that I have formulas in and would like to use.
So on one new invoice, it would contain the information from the 4 Smith rows, another invoice would be the 3 Thomas rows, etc.
The master invoice has different sections for the different information--the info can not be just transferred to a straight row.
Ie, I need the info from columns A-P in the master book to be in a straight row (columns A-P) for each of Smith's 4 rows in the new invoice.
Then I need the info from column S in the master book to be transferred to A23 in the new invoice sheet. Then column T gets transferred to F23. However, the S and T columns are dependent on the last name (Miller has 3 of his 6 rows filled out with text in column S, but Smith has nothing). It could be S13 and T13 from the master book, etc, but they will always get moved to the same A23 and F23 spots on the new sheets.

Secondly, can the tabs of the new invoice sheets get automatically named by the first column (last name). So ultimately I would have one sheet for Miller, 1 for Smith, etc.

I need to make a completely new master book every quarter with all new separate invoices, but all the titles will stay the same, so if anyone has some way to help, I'd appreciate it very much. Right now I have been copying and pasting everything.

And then lastly, would I be able to delete the master book once all the invoices are made without it affecting the invoices? (since all the info is being pulled from that master book)

 

[oldbrewer]

break up your problem into smaller pieces and ask how to do it stage by stage

eg only 2 names bill and fred and a few columns and rows on master sheet - what do you want to do with them - move them temporarily to master invoice sheet and the info from there to 2 sheets named bill and fred ???

 

[Rholly]

break up your problem into smaller pieces and ask how to do it stage by stage

eg only 2 names bill and fred and a few columns and rows on master sheet - what do you want to do with them - move them temporarily to master invoice sheet and the info from there to 2 sheets named bill and fred ???

Yes, essentially that is what I need, but the info in the columns from the master book need to be placed in specific areas on the invoices.

 

[oldbrewer]

if it must be "placed" vba is needed - but if those "places" on the invoice contain appropriate formulas they will pull the info automatically - which do you want

 

[Rholly]

The invoice formulas are only within the invoice, adding certain columns to get sums, etc. The existing formulas don't pull in data from anywhere. There are currently no formulas in the master book, only data. I understand what you are asking but I'm not sure which one would be easier. I think your option A "placed" with vba is best.

 

[Drrellik]

Rholly, welcome to the forum. in your original post you said you had 130 rows with 26 different names (I assume employees) and that you want to create a tab for each one, miller, smith, jones... this may take longer and not quite what you had in mind, but lets give this a thought or try. copy the entire master sheet (26) times once for each name and then delete the information you dont want on each sheet. meaning sheet2 = miller and delete all the smith, jones... infor from the sheet leaving the formulas and information for miller, and so on. this is how I might try to break it up so that all the formatting is the same. not sure this will help you but without seeing the data layout it is hard to picture what you have and help. Happy New year and again welcome to the forum. Don

 

[Rholly]

Don, thanks for the welcome and reply. That's not quite what I'm looking for though. The invoice has a different layout than the master sheet, so I need to place all the different names and info into specific places on individual invoices.

 

[mirabeau]

here's a VBA code I found in the attic somewhere.

it may do at least some of what you want

try it on a copy of your data in a new workbook

make sure your initially active sheet is your master sheet

Sub zecode()

Const cl& = 1   '1 here means colA, change to any other col as wanted
Dim lr&, lc&, s&, i&
Dim hdr, q As String, sh As Worksheet, ash as Worksheet

Application.ScreenUpdating = False
lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
s = 2
Set ash = ActiveSheet

With Sheets.Add(after:=ash)
    ash.Cells(1).Resize(lr, lc).Copy .Cells(1)
    hdr = .Cells(1).Resize(, lc)
    .Cells(1).Resize(lr, lc).Sort .Cells(cl), Header:=xlYes
    a = .Cells(cl).Resize(lr + 1)
    For i = 2 To lr
        If a(i, 1) <> a(i + 1, 1) Then
            q = CStr(a(i, 1))
            On Error Resume Next
            Set sh = Sheets(q)
            On Error GoTo 0
            If sh Is Nothing Then
                Sheets.Add(after:=Sheets(Sheets.Count)).Name = q
                .Cells(s, 1).Resize(i - s + 1, lc).Copy Sheets(q).Cells(2, 1)
                s = i + 1
                Sheets(q).Cells(1).Resize(, lc) = hdr
             End If
        End If
    Next i
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
End With

ash.Activate
Application.ScreenUpdating = True

End Sub

 

[Rholly]

Mirabeau, sorry for the long delay. I tried the code and it did split it into individually named sheets by their names, thank you! However, it didn't use my master invoice at all, so I still have no formatting or formulas. Would you be able to tell me how to incorporate the master invoice template so that when all the individual sheets are made, they automatically use the invoice template?

 

[oldbrewer]

name	basic	tax	total
tom	500	100	600
fred	600	120	720
bill	700	140	840
tom	800	160	960					basic	tax	total
fred	900	180	1080				tom	3800	760	4560
bill	1000	200	1200				fred	4200	840	4200
tom	1100	220	1320				bill	4600	920	4600
fred	1200	240	1440
bill	1300	260	1560
tom	1400	280	1680
fred	1500	300	1800
bill	1600	320	1920
			date and name are entered by you	31/01/2014
							tom
								INVOICE
							to:	tom
							date	31/01/2014
			all other fields automated		purchases	3800
							tax	760
							total	4560
							this invoice must be paid by:
							02/03/2014
							(30 days)

<colgroup><col span="7"><col span="2"><col span="3"></colgroup><tbody>
</tbody>