Copy ranges from multiple worksheets into one worksheet, in first empty cell

Question

So what I want to do is as follows:

I have a workbook that contains 2 worksheets with general information, then 30 worksheets with student information (student number, names, grades, final workgroup average), and then an overview sheet ("Overzicht-OSC").

What I want to do is copy only student number (in column C) and final workgroup average (in column L), and paste those values in my overview sheet ("Overzicht-OSC"). All workgroups contain a maximum of 25 students; often less, and the number varies per group. So what I want is to paste the numbers of the first group (in sheet 3) in the "Overzicht-OSC", then paste the numbers of the second group (in sheet 4) right under that information, etc., so that there will only be student numbers and grades in the final overview, skipping blank cells.

I wrote the following code for that:

Sub Overview()

Dim I As Integer
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

For I = 3 To 32

    Range("B8:B34,L8:L34").Copy
    Sheets("Overzicht-OSC").Select

        sourceCol = 1
        rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

        For currentRow = 1 To rowCount
            currentRowValue = Cells(currentRow, sourceCol).Value
            If IsEmpty(currentRowValue) Then
                Cells(currentRow, sourceCol).Select
                Exit For
            End If
        Next

    ActiveCell.PasteSpecial Paste:=xlPasteValues

Next I

End Sub

But it does not work! I keep getting various error messages. With the version as written above I get 'PasteSpecial method of Range class failed'.

When I change 'ActiveCell.PasteSpecial' into 'Selection.PasteSpecial' I get 'This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same size and shape'.

I have also previously tried a different code:

Sub Overzicht2()

Dim I As Integer

For I = 3 To 32

    Range("C8:C34,L8:L34").Select
    Selection.Copy
    Sheets("Overzicht-OSC").Select
    Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(1), Scroll:=True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next I

End Sub

This does not give an error message but also does not work.

How should I fix this?

SJR SJR · Accepted Answer · 2017-11-02T12:22:59

You're not referencing the sheets anywhere in your code and ActiveCell is not advisable as it's not clear which cell is active. Perhaps this will work, though I'd also be wary of using the sheet index as can easily be changed - better to use the sheet names or code names.

Sub Overzicht2()

Dim I As Long

For I = 3 To 32
    Sheets(I).Range("C8:C34,L8:L34").Copy
    Sheets("Overzicht-OSC").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next I

End Sub

Copy ranges from multiple worksheets into one worksheet, in first empty cell

1 Answers