vba expained

[Peterfc2]

With Sheets("uniques")
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
    If sh.Name Like "*-*" Then
        sh.Range("B10", sh.Cells(Rows.Count, 2).End(xlUp).Offset(-3)).Copy Sheets("uniques").Cells(Rows.Count, 1).End(xlUp)(2)
    End If
Next
End With

My sheets are seasons in the format 2019-20

This has worked fine but now give run time error 1004

sh.Range("B10", sh.Cells(Rows.Count, 2).End(xlUp).Offset(-3)).Copy Sheets("uniques").Cells(Rows.Count, 1).End(xlUp)(2)

Can someone break this row down. I have sheets with no data I think this could be the problem.
Cheers

 

[Domenic]

When a worksheet is empty, sh.Cells(Rows.Count, 2).End(xlUp) will refer to B1. And so when you try to offset this range upwards by 3 rows, you'll get an error. Therefore, you should first find the last used row in Column B, and then decide whether to copy/paste.

By the way, in your example, you're looping through the Sheets collection. However, if your workbook contains a sheet other than a worksheet (ie. Chart sheet), you'll get an error. Therefore, I would suggest you loop through the Worksheets collection instead.

Let's say that you want to copy the data only if data exists in cells B10 and beyond. In this case, your code can be re-written as follows...

    Dim sh As Worksheet
    Dim lastRow As Long
    
    For Each sh In ThisWorkbook.Worksheets
        With sh
            If .Name Like "*-*" Then
                lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
                If lastRow >= 10 Then
                    .Range("B10:B" & lastRow).Copy Sheets("uniques").Cells(Sheets("uniques").Rows.Count, 1).End(xlUp)(2)
                End If
            End If
        End With

    Next sh

Hope this helps!

 

[Peterfc2]

Thanks for the explanation. Will give it a try soon .