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

[Closed]
Report
Posts
1
Registration date
Wednesday March 9, 2016
Status
Member
Last seen
March 9, 2016
-
Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
-
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


1 reply

Posts
2782
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 2, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!