Excel- How do I auto transfer rows of data to another worksheet Closed

Question

Hello all,

I am after some help with excel. I am a PE teacher and need to find a way to cut down the processing time for our athletics carnival scoring system. One way is to use excel to automatically sort through some of the data for me. The problem is Im not savvy enough to set this up and so some help would be great.


What I want to do:
Below is an example of how I would like to set out my worksheets. There will be approx 12 worksheets (one for each form) and they will be set up as seen below. 

Worksheet 1 (form M1)

                  Discus Event

 [Row 1]     Student Name, Year, Form, Throw 1, Throw 2, Throw 3, Best Throw

 [Row 2]     e.g jonny         8       M1     14m        15m      16m       16m  


Worksheet 2 (form G1)

                  Discus Event

 [Row 1]     Student Name, Year, Form, Throw 1, Throw 2, Throw 3, Best Throw

 [Row 2]     e.g Jacko         8       M1     13m        15m      18m       18m  


Final Scores
                  Discus Event

[row 1]      Student Name, Year, Form, Best Throw



As seen above the last worksheet will be for the final scores. Essentially what I need is a way to send each row of student data from EACH worksheet to the "Final Scores" worksheet AND once it is at the final worksheet for it to sort itself from largest throw to smallest throw. 
The purpose of this is so that after all of the data is input int other spreadsheet, I can order the best throws from highest to lowest and use the top 10 throws in my PE scoring system. 

I cannot lose track of which student has thrown what and so all the data must stay together.

Some help would be great. 
cheers.

DP


Configuration: Mac OS X Lion (10.7.5) / Safari 6.1.6

TrowaD · Answer

Hi Dpirovich,

The following code can be run from any sheet. It also doesn't matter how many forms you might add (if the need arrises).

Here is the code:
Sub RunMe()
Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Name <> "Final Scores" Then
        ws.Select
        Range("A2:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy _
        Sheets("Final Scores").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Range("G2:G" & Range("G" & Rows.Count).End(xlUp).Row).Copy _
        Sheets("Final Scores").Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next ws

Sheets("Final Scores").Select
Range("A1").CurrentRegion.Sort key1:=Range("D1"), order1:=xlDescending, Header:=xlYes

End Sub

And in case you don't know:
How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.


Best regards,
Trowa

Excel- How do I auto transfer rows of data to another worksheet

1 response

Similar discussions

Subscribe To Our Newsletter!