Method 'range' of object 'Worksheet' failed

Question

I have an excel file with 8 sheets and I want to combine the range with data from "adws","chws","homelines","ariel","kit","philip" and "timmy" to the sheet "ws", except the header.

Here is my code:

Private Sub combine_btn_Click()
Dim ws As Worksheet
Dim adws As Worksheet
Dim chws As Worksheet
Dim homelines As Worksheet
Dim ariel As Worksheet
Dim kit As Worksheet
Dim philip As Worksheet
Dim timmy As Worksheet
Dim adws_co As Long
Dim chws_co As Long
Dim homelines_co As Long
Dim ariel_co As Long
Dim kit_co As Long
Dim philip_co As Long
Dim timmy_co As Long
Dim ws_co As Long

Set ws = ThisWorkbook.Sheets("Data")
Set adws = ThisWorkbook.Sheets("SL - Adult")
Set chws = ThisWorkbook.Sheets("SL - Children")
Set homelines = ThisWorkbook.Sheets("Homelines & Acc")
Set ariel = ThisWorkbook.Sheets("Hardlines - Ariel")
Set kit = ThisWorkbook.Sheets("Hardlines - Kit")
Set philip = ThisWorkbook.Sheets("Hardlines - Philip")
Set timmy = ThisWorkbook.Sheets("Hardlines - Timmy")

ws_co = ws.Range("A2:F" & Rows.Count).End(xlDown).Row + 1
adws_co = adws.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
chws_co = chws.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
homelines_co = homelines.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
ariel_co = ariel.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
kit_co = kit.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
philip_co = philip.Range("D2:F" & Rows.Count).End(xlDown).Row + 1
timmy_co = timmy.Range("D2:F" & Rows.Count).End(xlDown).Row + 1


adws.Range("D2:F" & adws_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

chws.Range("D2:F" & chws_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

homelines.Range("D2:F" &homelines_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

ariel.Range("D2:F" & ariel_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

kit.Range("D2:F" & kit_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

philip.Range("D2:F" & philip_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

timmy.Range("D2:F" & timmy_co).SpecialCells(xlCellTypeVisible).Copy

ws.Select
Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

However, when the sheet "philip" is empty, I received a failure "Method 'Range' of object '_Worksheet' failed on this line:

philip.Range("D2:F" & philip_co).SpecialCells(xlCellTypeVisible).Copy

May I know is there any way to solve it? Thanks in advance!

Answer 1

Maximum row in Excel is 1048576. If "philip" sheet is empty, it means that philip_co takes a value of 1048577 (as you add 1 to the function). Now, if you try to select/copy a range of D2:F1048577, Excel will throw an error as this range simply doesn't exist.

Answer 2

Even though this is not the answer for your problem, you already referenced the sheets, so you don't need to select them in any way. Just do it like this:

Private Sub combine_btn_Click()

    Dim ws As Worksheet
    Dim adws As Worksheet
    Dim chws As Worksheet
    Dim homelines As Worksheet
    Dim ariel As Worksheet
    Dim kit As Worksheet
    Dim philip As Worksheet
    Dim timmy As Worksheet
    Dim adws_co As Long
    Dim chws_co As Long
    Dim homelines_co As Long
    Dim ariel_co As Long
    Dim kit_co As Long
    Dim philip_co As Long
    Dim timmy_co As Long
    Dim ws_co As Long

    With ThisWorkbook
        Set ws = .Sheets("Data")
        Set adws = .Sheets("SL - Adult")
        Set chws = .Sheets("SL - Children")
        Set homelines = .Sheets("Homelines & Acc")
        Set ariel = .Sheets("Hardlines - Ariel")
        Set kit = .Sheets("Hardlines - Kit")
        Set philip = .Sheets("Hardlines - Philip")
        Set timmy = .Sheets("Hardlines - Timmy")
    End With

    ws_co = ws.Cells(ws.Rows.Count, 6).End(xlUp).Row + 1
    adws_co = adws.Cells(adws.Rows.Count, 6).End(xlUp).Row + 1
    chws_co = chws.Cells(chws.Rows.Count, 6).End(xlUp).Row + 1
    homelines_co = homelines.Cells(homelines.Rows.Count, 6).End(xlUp).Row + 1
    ariel_co = ariel.Cells(ariel.Rows.Count, 6).End(xlUp).Row + 1
    kit_co = kit.Cells(kit.Rows.Count, 6).End(xlUp).Row + 1
    philip_co = philip.Cells(philip.Rows.Count, 6).End(xlUp).Row + 1
    timmy_co = timmy.Cells(timmy.Rows.Count, 6).End(xlUp).Row + 1


    With ws
        adws.Range("D2:F" & adws_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        chws.Range("D2:F" & chws_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        homelines.Range("D2:F" & homelines_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        ariel.Range("D2:F" & ariel_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        kit.Range("D2:F" & kit_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        philip.Range("D2:F" & philip_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        timmy.Range("D2:F" & timmy_co).SpecialCells(xlCellTypeVisible).Copy
        .Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With

End Sub

Also, without further info your code looks ok to me.

Edit: Sorted with Justyna answer, didn't see that one!