UBound coll error after converting macro. Runtime error 13. Type mismatch.

Hello all,

Merry Christmas! I hope you all are safe and healthy.

I have an existing macro which copies 4 distinct ranges from a filtered table from one worksheet to another worksheet in the same workbook. This works correctly.

I'm now trying to convert this to be able to do that from a filtered table in one worksheet to another worksheet in another workbook.

I think I was able to handle that first part correctly as the first copy module runs correctly and data is copied before I get the "Type mismatch" error message. But if I understand the code correctly, there's a loop at the end where I now get issues for some reason. Because of privacy issues, I'm unable to upload the actual workbook, but I can post the image of the layout with the code. This might be a shot in the dark, but I'm thinking it should be a small adjustment that someone clever in VBA can spot quickly.

If not, my apologies and I can remove the post.

It's this part of the code which triggers an error:

For i = 1 To coll.Count
    For j = 1 To UBound(coll(i), 2)
        res(i, j) = coll(i)(1, j)

Thanks in advance.

2.png

Sub CopyFilteredDataMacroMainLegPartDay2()
Dim R1 As String
R1 = "A:G"

Dim dest1 As String
dest1 = " CE63"

Dim R2 As String
R2 = "R:U"

Dim dest2 As String
dest2 = "CL63"

Dim R3 As String
R3 = "Z:AI"

Dim dest3 As String
dest3 = " CP63"

Dim R4 As String
R4 = "AM:CN"

Dim dest4 As String
dest4 = " CZ63"

Call CopyFilteredDataMacroMainLegPartDay(R1, dest1)
Call CopyFilteredDataMacroMainLegPartDay(R2, dest2)
Call CopyFilteredDataMacroMainLegPartDay(R3, dest3)
Call CopyFilteredDataMacroMainLegPartDay(R4, dest4)
End Sub

Sub CopyFilteredDataMacroMainLegPartDay(R As String, dest As String)

Dim M_M, FilteredData As Worksheet
Set M_M = ThisWorkbook.Sheets("Macro & MainLeg")
Set FilteredData = Workbooks("FullDayMacro&MainLeg.xlsm").Sheets("PartDayFilteredData")
Dim rng As Range
Dim res, ar
Dim coll As New Collection
For Each rng In ActiveWorkbook.ActiveSheet.ListObjects(1).DataBodyRange.Columns(R).SpecialCells(xlCellTypeVisible).Rows
    ar = rng.Value
    coll.Add ar
Next
ReDim res(1 To coll.Count, 1 To UBound(ar, 2))

Dim i As Integer
Dim j As Integer


For i = 1 To coll.Count
    For j = 1 To UBound(coll(i), 2)
        res(i, j) = coll(i)(1, j)
    Next
Next
FilteredData.Range(dest).Resize(UBound(res), UBound(res, 2)) = res
End Sub