[Year 12 IT Apps] Macros in Excel

[Graham]

G'day

In relation to Macros:

As the timetabler, I have a copy of the timetable in Excel, one day  
per worksheet.  I have a macro that will start at Monday's sheet,  
print Monday's timetable, go to Tuesday's worksheet, print it and so  
on down to Friday, and then return to Monday. (Yes I could have them  
all on the one sheet, but this works better for me for formatting  
reasons.)

In relation to student work, I once had a student SAC that (from  
memory) used one macro to

    1. Collect salary data for an employee by copying it from  
relevant worksheets
    2. Paste that data into a Payslip worksheet
    3. Remove the colour (for printing purposes)
    4. Print the payslip
    5. Copy the data
    6. Open a new workbook
    7. Paste the data
    8. Save the workbook using employee name and the date  
(070729Hughes_G.xls)
    9. Close this workbook
  10. Return to original workbook
  11. Clear the data cells on Payslip worksheet
  12. Collect data about next employee
  13. Repeat steps 2 - 11 until all employees were done

This provided a payslip for each employee plus a saved copy of each  
paysjip for the business.

Once the above macro was completed, a second macro was run.  It  
initiated a ' Save as' for the original workbook, saving it using the  
date as the file name e.g. 070729Pays.xls.  This was closed and then  
the original workbook was re-opened all the data cleared, readying it  
for the next week's data, and then it was saved.

I think that the above is accurate ... apologies if my memory is  
faulty ....

On a personal note, I, in response to a challenge from my class, once  
wrote a macro that

    1.  Copied a weekly salary figure from a worksheet
    2.  Pasted it in to a worksheet, 'Conversion',  that used a  
complex series of formulae to convert the number to words
    3.  Went to a worksheet called 'Cheque' and pasted, individually,  
the salary figure in numbers and words into a cheque
    4.  Retrieved the employee's name and pasted it into 'Cheque' in  
the appropriate place
    5.  Printed the cheque ready for signature (date was done using a  
function).  I tried but did not succeed in getting the cheque numbers  
to increment after each printing.
    6.  Cleared the data from 'Conversion' and  'Cheque', ready for  
the next employee


I hope this is useful ...



Graham Gales
Bright P-12 College
- - - - - - - - - - - - - - - - - - -
ITA, Science and Maths Teacher
Timetabler/ Daily Organiser
Gifted & Talented Co-ordinator
Heir apparent to the Chief Cook and BottleWasher
Understudy to the general Dogsbody



On 25/07/2007, at 3:37 PM, Russell Edwards wrote:

> Does anyone have any thoughts on common uses of macros in Excel?
>
> I am struggling to come up with any examples that are generally  
> applicable, and therefore worth putting in to student exercises.     
> I use macros occasionally but they are generally quite specific to  
> the particular job that spreadsheet is designed for.
>
> About all I can think of would be setting the active cell to the  
> first input data cell upon opening, and inter-sheet navigation  
> buttons.
>
> thanks in advance
>
> Russell
> Whittlesea SC
>


Important - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20070725/59b17120/attachment-0001.html