Cutting and pasting (and deleting rows) between multiple sheet spreadsheet with macros.

[t20racerman]

Hi all

I really hope you can help. I'm a Physics teacher using an exam board generated spreadsheet in which I can keep track of all my student's practical work over a minimum of 12 assessed practicals - I have to keep these records as part of their assessment. You can download the spreadsheet I'm using here to see what I mean:

http://filestore.aqa.org.uk/resources/physics/AQA-7408-EET.XLSX

In the L6th form I had three of these sheets - one for each group, and all went well. However, in the U6th, some students dropped the subject and the groups were all swapped around so that the remaining students are in different groups.

I've tried deleting students who've dropped the subject - and cant. It just messes up the spreadsheet.
I need to move students and their data from one sheet to another - and can't see how to do so.
Alternatively, could I merge the three sheets somehow and have them all on the same sheet if that is easier?

The spreadsheet was put together by the AQA exam board. I phoned their advisors for help and was told "find someone who knows Excel"! They offer no support whatsoever.

I could set up new spreadsheets for my U6th groups, but would then need to cut and paste all the filled in data across from last year's work.

I would appreciate any help or guidance here.

Thanks
Adrian

 

[Snakehips]

Adrian,

See if the below helps by semi-automating the process.
Do backup original files before testing.

Assumes ....
You have 3 files for Lower 6 Groups
Number of sheets in all files is same - 19
Tabs are in same order as the original blank file.
3 new groups for U6

Create 3 fresh file and name U6G1, U6G2 & U6G3, having cleared the sample data as appropriate.
Have all three of these files open.

Open one of the old L6 group files and select the Attendance sheet.
In column Y indicate for each student.... 1 for U6 group 1, 2 for group 2 and 3 for 3 and 'x' for any student who is dropping the subject. Code will ignore rows that you do not give a 1, 2, ,3 or x.

Copy the below code to the code module for the Attendance sheet. (Right click sheet tab > View Code > Paste Code)

Then run the code by whatever means e.g. from the Run dropdown >> Run Sub / Form

That should hopefully transfer student data to appropriate new workbook and clear their data from the original.
Clear the data codes from column Y

Then repeat for the other two L6 files.

It's not 100% slick but I hope it helps the process.

Sub ReGroup()
Set WBLS = ThisWorkbook  'Old Lower 6 group


Set CODE = WBLS.Sheets("Attendance").Range("Y4:Y30")
For Each cd In CODE
Ignore = 0
    Select Case cd
        Case 1
            Set WBG = Workbooks("U6G1")  ' new Upper 6 Group 1
        Case 2
             Set WBG = Workbooks("U6G2")
        Case 3
             Set WBG = Workbooks("U6G3")
        Case Else
        Ignore = 1
    End Select
 If Ignore = 0 Then
 'Transfer dates  --  repetative but no problem
    WBG.Sheets("Attendance").Range("B2:P3").Value = WBLS.Sheets("Attendance").Range("B2:P3").Value
    'Transfer Attendance detail
    r = cd.Row
       lr = WBG.Sheets("Attendance").Range("A30").End(xlUp).Row + 1
    WBG.Sheets("Attendance").Range("A" & lr & ":P" & lr).Value = WBLS.Sheets("Attendance").Range("A" & r & ":P" & r).Value
    'Clear Orgiginal
        WBLS.Sheets("Attendance").Range("A" & r & ":P" & r).ClearContents
     'Note row as moved
        WBLS.Sheets("Attendance").Range("A" & r).Value = "Moved to U6 Group " & cd
    'Transfer Practical detail


    For n = 5 To 19
        'Notes & Dates -- 'repeticious but does not matter
        WBG.Sheets(n).Range("B2:F4").Value = WBLS.Sheets(n).Range("B2:F4").Value
        WBG.Sheets(n).Range("I2:I3").Value = WBLS.Sheets(n).Range("I2:I3").Value
        'Student detail
        WBG.Sheets(n).Range("B" & lr + 2 & ":F" & lr + 2).Value = WBLS.Sheets(n).Range("B" & r + 2 & ":F" & r + 2).Value
        'Clear Orgiginal
        WBLS.Sheets(n).Range("B" & r + 2 & ":F" & r + 2).ClearContents
        
    Next n
 End If


Next cd






End Sub

Edit:

Old files then history of dropout students only.

 

[t20racerman]

Thank you so much for that! I'll have a play later this week and see what happens. Thank you so much for making such an effort to help.

Adrian

 

[t20racerman]

Hi there

I had a go at what you said and it didn't quite work as planned, unfortunately. On the new U6th sheets the transferred students and their data went in the rows above and below the Student name row - ie the first one went in row 3 (above student name 1) and the others went below student name 25 ie in rows 29 and 30.

The names and data did move across though!
Just a thought - there will also be conflicting dates, as some did them on different days to others, but that data is non-essential as we have a written copy of all of these.

 

[Snakehips]

Oh dear.

I tested only on the first L6 file which seemed to be fine. Please confirm if you are able.
I think I've just twigged why it's screwing up when you do the next two files. Will not be difficult to rectify.
I can't devote any time to it right now but will look at it later.

 

[Snakehips]

Adrian, sorry for delay.
I have re-tested and as far as I can tell all works fine for me.
My thoughts on your results are as follows.
I can understand how data is going to rows 29 , 30 etc......... the code needs for you to have cleared the sample names, Student 1 - Student 25 from the Attendance sheet of the three new files* as it looks upwards in that column in order to determine the next available row. With 'Student 25' in row 28 it will take row 29 as the next available row!
As for putting data in row 3 then I think that will be a result of one or other of your groups having more than what I took to be maximum of 25 students ??????
If so that will cause an error in the next row calculation and return a value of 3.

I have tweaked the code as below to ignore a student numbers max but it will still be necessary for you to clear the sample 'Student Names' from the new files before you run code.
As for different dates of same project for the original L6 groups -- I see no way you can accommodate that if the student mix is now so different.

Edit * I Forgot to make that clear in original post
Have another pop and see how it goes.

Sub ReGroup()
Set WBLS = ThisWorkbook  'Old Lower 6 group


If Not ActiveSheet.Name = "Attendance" Then Exit Sub
Set CODE = WBLS.Sheets("Attendance").Range("Y4:Y30")
For Each cd In CODE
Ignore = 0
    Select Case cd
        Case 1
            Set WBG = Workbooks("U6G1")  ' new Upper 6 Group 1
        Case 2
             Set WBG = Workbooks("U6G2")
        Case 3
             Set WBG = Workbooks("U6G3")
        Case Else
        Ignore = 1
    End Select
 If Ignore = 0 Then
 'Transfer dates  --  repetative but no problem
    WBG.Sheets("Attendance").Range("B2:P3").Value = WBLS.Sheets("Attendance").Range("B2:P3").Value
    'Transfer Attendance detail
    r = cd.Row
       lr = WBG.Sheets("Attendance").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
    WBG.Sheets("Attendance").Range("A" & lr & ":P" & lr).Value = WBLS.Sheets("Attendance").Range("A" & r & ":P" & r).Value
    'Clear Orgiginal
        WBLS.Sheets("Attendance").Range("A" & r & ":P" & r).ClearContents
     'Note row as moved
        WBLS.Sheets("Attendance").Range("A" & r).Value = "Moved to U6 Group " & cd
    'Transfer Practical detail




    For n = 5 To 19
        'Notes & Dates -- 'repeticious but does not matter
        WBG.Sheets(n).Range("B2:F4").Value = WBLS.Sheets(n).Range("B2:F4").Value
        WBG.Sheets(n).Range("I2:I3").Value = WBLS.Sheets(n).Range("I2:I3").Value
        'Student detail
        WBG.Sheets(n).Range("B" & lr + 2 & ":F" & lr + 2).Value = WBLS.Sheets(n).Range("B" & r + 2 & ":F" & r + 2).Value
        'Clear Orgiginal
       WBLS.Sheets(n).Range("B" & r + 2 & ":F" & r + 2).ClearContents
        
    Next n
 End If




Next cd




End Sub

 

[t20racerman]

Wow! You Sir, are a coding angel sent to help a poor sole like me who hasn't a clue! It worked beautifully and did exactly what I wanted. So happy.

Thank you, thank you, thank you

 

[Snakehips]

Adrian,

You are welcome. I'm pleased it helped.