No cells were found - trying to copy a hidden row range

[Forsberg]

Hello,

I am fighting with No cells were found issue. I have a filter on A17 and data begins at the 18th row. There is no issue until I filter some data and rows go like this:

18
19
41
45

Then the macro will copy only the first 2 rows, while the rest will give an error. All sources I found didn't help me unfortunately.

I tried this:

Set copyRange = srcWS.Range("A" & destLastRow & ":I" & (destLastRow + rowsToCopy - 1)).SpecialCells(xlCellTypeVisible)

I thought SpecialCells would solve this but it is no go. I used also On error resume next, but it just hides the error, it doesn't copy cells the way I want.

The whole idea is: I have a table with people name's. I choose people by just writing a name in the B4:B13 range. Then I paste data rows below A18 (filter on A17. I filter some data leaving those rows I want. Then some calculation is being done and macro should spread the visible rows across all people in the table (equally and without repeating the same data).


My whole sheet code looks like this:

Sub CreateSheetsStyled()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet
    Dim destWS As Worksheet
    Dim cell As Range
    Dim copyRange As Range
    Dim pasteRange As Range
    Dim lastRow As Long
    Dim destLastRow As Long
    Dim colorIndex As Long 
    Dim rowsToCopy As Long

   Set srcWS = ThisWorkbook.Sheets("Cover_data")
    
    lastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row
    destLastRow = 18 
    
    Dim tabColors As Variant
    tabColors = Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12) 
    
    For Each cell In srcWS.Range("B4:B13") 'people names
        If cell.Value <> "" Then
            If WorksheetExists(cell.Value) Then
                Set destWS = ThisWorkbook.Sheets(cell.Value)
            Else
                Set destWS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                destWS.Name = cell.Value
            End If
            
            rowsToCopy = WorksheetFunction.RoundUp(srcWS.Range("G4").Value, 0) 'rows count per people (how many per single person)
            
            Set copyRange = srcWS.Range("A" & destLastRow & ":I" & (destLastRow + rowsToCopy - 1)).SpecialCells(xlCellTypeVisible)
            
            Set pasteRange = destWS.Range("A" & destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row + 1)
            
            copyRange.Copy pasteRange
            destLastRow = destLastRow + rowsToCopy 
            Application.CutCopyMode = False
            
            If colorIndex <= UBound(tabColors) Then
                destWS.Tab.colorIndex = tabColors(colorIndex)
                colorIndex = colorIndex + 1
            Else
                destWS.Tab.colorIndex = tabColors(UBound(tabColors))
            End If
        End If
    Next cell
End Sub

 

[Micron]

Not sure, but from other source I've read you need to copy the filtered range (using visible as you have done) then .PasteSpecial into the target range.
I'm basing that on the first line you posted not being the line that generates an error.

 

[Forsberg]

I tried many approaches and all of them fail when they find a hidden row. I even removed all people and left only 1 so script wouldn't have to spread all cases across all members.
There is no error then, but it still copies only the 18 and 19 rows while leaving 41 and 45 uncopied. I used pastespecial.

 

[Micron]

I used pastespecial.

sorry, I didn't see that in your code (still don't but I tend to glance over things).
Hope you get a solution.