I need my program to copy 3 cells and paste it as a Picture on the next sheet. I need it to repeat t-CodePudding

Every year teachers get together to create new class lists for the next year. This is currently done using Post It notes. The classes need to have a spread of boy/girl numbers, a spread of academic levels and spread of behaviour levels. This is important so as not to weight classes with lots of high behaviour students as an example. I currently get a download of all students to excel. They are labelled on gender. Teachers then rate each each of their children academically and behaviourally using a colour coding. I copy the first child's name, their academic rating and their behaviour rating copied and pasted as a Picture onto sheet 2. This is where I need help please. I need this to happen to every student in the list. Teachers can then move students on sheet 2 around into classes and have a visual indication of the balance based on gender, academic and behaviour levels.

Sub Macro1()
'
' Macro1 Macro
'
    Range("B2:D2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Pictures.Paste.Select
End Sub

••••ˇˇˇˇ

Student class layout

CodePudding user response：

Try this:

Sub Macro1()
    Dim rw As Range, wsSrc As Worksheet, wsDest As Worksheet, t As Long
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet1")  'sheet with table
    Set wsDest = ThisWorkbook.Sheets("Sheet2") 'sheet for pasting pictures
    
    t = 10
    'loop over all the input rows
    For Each rw In wsSrc.Range("B2:D" & wsSrc.Cells(Rows.Count, "B").End(xlUp).Row).Rows
        rw.Copy
        wsDest.Pictures.Paste
        With wsDest.Shapes(wsDest.Shapes.Count) 'the last-pasted picture
            .Top = t              'make sure they're not all stacked up...
            t = t   rw.Height   5 'increment top position
        End With
    Next rw
End Sub