If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Mastersheet to Worksheet data copy - macros?
I'm out in Tanzania working for a school and I'm helping out with the school
accounts. Up until now there has been a lot of fraud so we are trying to consolidate the information and put it all on excel or perhaps access to ensure that we have an electronic record, as the cashier and accountant have been using hard records and a lot of money has been going walk-about. We have created an excel document with a master copy listing all 460 students on one worksheet. The categories a FORM, FORM STREAM, SEX, STUDENT TYPE, BOARDER OR DAY STUDENT, SPONSORED, CATEGORY OF SPONSORSHIP, FIRST NAME, LAST NAME. We then have 1st Quarter Fees and whether or not they are in credit or debit, going all the way to 4th Quarter. We also want to have Pocket Money and Medical Expenses on the same Worksheet. Here's the catch. We think the easiest way to do everything right now, is to print off a page containg the student's NAME, FORM, B/D, SPONSORED?. And then we would have two tables, one listing the School Fees details and another listing Pocket Money and Medical Expenses. Is there anyway of creating a macro or some sort of rule that states: Please create 460 sheets from the mastercopy containing NAME, FORM, B/D, SPONSORED and both tables? Please help!! Thank you -- DCT Mvumi School |
#2
|
|||
|
|||
Mastersheet to Worksheet data copy - macros?
The code below expects the master sheet in the workbook to be called
"Summary" and ther is a 2nd worksheet called Template that will be copied for each student. The code creates the worksheet name using the student last and first name. I would sort the students by first anem and last name before running macro so the worksheets are alphebitzed to make it easier to find a stundent. There is no limit to the the number of worksheets that excel can use except the more sheets you have the more memory you need on your PC. Sub CreateSheets() 'Create a template sheet to be copied for each student Set TemplateSht = Sheets("Template") Set SummarySht = Sheets("Summary") 'skip header row on summary sheet SumRowCount = 2 With SummarySht Do While .Range("A" & SumRowCount) "" LastName = .Range("A" & SumRowCount) FirstName = .Range("B" & SumRowCount) SPONSORED = .Range("C" & SumRowCount) 'add more data here that you want to copy from summmary sheet 'Add New sht by copying template TemplateSht.Copy after:=Sheets(Sheets.Count) Set NewSht = ActiveSheet 'change name or workshheet to match studnet name NewSht.Name = LastName & "_" & FirstName With NewSht 'add more code here to put data into new worksheet .Range("B2") = SPONSORED End With SumRowCount = SumRowCount + 1 Loop End With End Sub "DCT Mvumi School" wrote: I'm out in Tanzania working for a school and I'm helping out with the school accounts. Up until now there has been a lot of fraud so we are trying to consolidate the information and put it all on excel or perhaps access to ensure that we have an electronic record, as the cashier and accountant have been using hard records and a lot of money has been going walk-about. We have created an excel document with a master copy listing all 460 students on one worksheet. The categories a FORM, FORM STREAM, SEX, STUDENT TYPE, BOARDER OR DAY STUDENT, SPONSORED, CATEGORY OF SPONSORSHIP, FIRST NAME, LAST NAME. We then have 1st Quarter Fees and whether or not they are in credit or debit, going all the way to 4th Quarter. We also want to have Pocket Money and Medical Expenses on the same Worksheet. Here's the catch. We think the easiest way to do everything right now, is to print off a page containg the student's NAME, FORM, B/D, SPONSORED?. And then we would have two tables, one listing the School Fees details and another listing Pocket Money and Medical Expenses. Is there anyway of creating a macro or some sort of rule that states: Please create 460 sheets from the mastercopy containing NAME, FORM, B/D, SPONSORED and both tables? Please help!! Thank you -- DCT Mvumi School |
Thread Tools | |
Display Modes | |
|
|