How to create many worksheets from master, Excel 2010

Rholly · Jan 2, 2014

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)

Rholly · Jan 3, 2014

Yes, the bottom part of that is basically what I'm looking for. I already have a master invoice with the sum formulas that need to be used when all the individual sheets are made.

Rholly · Jan 3, 2014

For the purchases and tax amt that you used, I need to put those in specific cells.

Rholly · Jan 3, 2014

First

Last

Course Name

Course Title

Instruction Method

Course Credits

Contact Hours/Week

Workload Hours

Start Date

End Date

Days

Start Time

End Time

Building

Room Number

Actual Students

Totals=

0.00

Students in Class=

0

Plus Supplemental=

0.00

Advisees=

Total of All=

0.00

Total Students=

0

Minus 12

-12

Overload =

0.00

Committee Assignments:

Scholarly Work:

Community Work:

Supplemental Projects Under Contracted Load:

Project Name

Course Credits

Contact Hours/wk

Workload Hours

% of Load

Start Date

End Date

Nature of Project

Totals=

0.00

Rholly · Jan 3, 2014

The above is my master invoice. This is what I need each person to have. So the columns at the top (first, last, course name, etc) are a straight pull from the master, which is what the vba did earlier. However, Students in Class, Advisees, Committee Assignments, and the Supplemental info all need to pulled from specific columns in the master list under each name.

oldbrewer · Jan 3, 2014

I do not know what you want, sorry. I gathered summary info for each name from a table of raw data and then mocked up an invoice - your real invoice can pull the data correctly - it can then be copied and pasted to a desired location and a new name / date chosen....

Rholly · Jan 3, 2014

oldbrewer said:
- move them temporarily to master invoice sheet and the info from there to 2 sheets named bill and fred ???

I need to use BOTH the master list of everybody AND the master invoice to then make individual sheets for each person that will be formatted like my master invoice.

oldbrewer · Jan 3, 2014

the master invoice can only deal with one person at once so why is my post #13 not ok - the detailed formatting is down to you - a macro could print off the invoice for each name and change the names / dates

Rholly · Jan 3, 2014

Yes, the macro listed before already gave individual sheets. Maybe I'm not smart enough

to figure out how to insert formatting from that, so I keep referring to my master invoice which is already formatted. Your post number 10 is basically what I need, but I don't know how to accomplish that.....

oldbrewer · Jan 4, 2014

in post 10 the summary table uses sumproduct formulas and the invoice table uses offset(match) formulas PM me your Email and I will send you the spreadsheet but better for you long term if you figure it out yourself - sumproduct is a very useful formula.

Rholly · Jan 8, 2014

Someone else was able to help me and it is a simple solution, really just one line of code was missing. I've posted the code and the part I made blue brings in the template. This ultimately starts from a master list, and then by using a template, makes individual sheets with everybody's names formatted to that template. This is exactly what I needed. The template already has all the formulas/sums and conditional formatting.

 Sub Newcode()

Const cl& = 2 '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))
Sheets.Add(after:=Sheets(Sheets.Count), Type:="C:\YOUR TEMPLATE FILE NAME HERE.xlsx").Name = q
.Cells(s, 1).Resize(I - s + 1, lc).Copy Sheets(Sheets.Count).Cells(2, 1)
s = I + 1
Sheets(q).Cells(1).Resize(, lc) = hdr
End If
Next I
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

ash.Activate
Application.ScreenUpdating = True

End Sub

How to create many worksheets from master, Excel 2010

Rholly

New Member

Rholly

New Member

Excel Facts

Rholly

New Member

Rholly

New Member

Rholly

New Member

oldbrewer

Well-known Member

Rholly

New Member

oldbrewer

Well-known Member

Rholly

New Member

oldbrewer

Well-known Member

Rholly

New Member

Similar threads

Forum statistics

Share this page

How to create many worksheets from master, Excel 2010

New Member

New Member

Excel Facts

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock