How to copy info from some number of preset worksheets into one overview page?

user_15117764 0
Viewed 35

I have a Userform to fill out a bit of information into a set 22 rows with it's own specific sheet. I'd then like to copy those 22 rows into an overview worksheet.

I tried to skip down 23 slots so there would be 1 row inbetween for viewability.

I get

Run-time error '438': Object doesn't support this property or method

and targets the line after my For statement, starting with Worksheets.

Sub populateOverview()

    Dim i As Integer
    Dim x As Integer
    Dim y As Integer
    
    Let y = i - 4

    For i = 4 To Sheets.Count
        Worksheets(i).Range("1:22").Copy Worksheets("Overview").Cell(y)
        y = y * 24
    Next i

End Sub
vba excel
1 Answers

Why do you have this?

Let y = i - 4

It will set y to the value -4 initially and then within the loop it will be set to -96, -2304, -55296 and so on.

Even if you do change Cell to Cells, as suggested, it isn't going to work.

Give this a try.

Sub populateOverview()
Dim rngSrc As Range
Dim rngDst As Range

    Set rngDst = Sheets("Overview").Range("A1")
    
    For i = 4 To Sheets.Count
        Set rngSrc = Sheets(i).Range("1:22")
        
        rngSrc.Copy rngDst
        
        Set rngDst = rngDst.Offset(23)
        
    Next i
    
End Sub
user_28500260
Related