0

My Excel workbook has a 'summary' sheet with a column of scores for each student (starting from column I). The first row of each column has the student's name. A macro has created a separate sheet for each student, with their name copied to cell D4 of their own sheet. I want to copy each student's column of scores from the 'summary' sheet to E11:E26 in their own sheet. I have tried the following but I am very new to this - any help welcomed!


Dim i As Integer, sh As Worksheet, shSumm As Worksheet
Set shSumm = ThisWorkbook.Worksheets("summary")

For Each sh In ThisWorkbook.Worksheets
    For i = 9 To 50
        If shSumm.Cells(1, i).Value2 = sh.Cells("D4").Value2 Then
            sh.Range("E11:E26") = shSumm.Range(Cells(2, i), [Cells(17,i)])
            
        End If
    Next i
Next sh
            
    
End Sub
194
  • 1
  • 2
    https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet – BigBen Dec 18 '20 at 14:58
  • I don't think you can do that range assignment. Instead, you probably have to iterate through each cell in the range, and assign the values on a cell-by-cell basis. Alternatively, you might be able to invoke copy and paste commands/functions from VBA for what you want to do. Unfortunately, I have no access to Excel today, so can't much help you further. – Mark Fernandes Dec 18 '20 at 17:08

0 Answers0