VBA listobjects databodyrange selection with filtered columns

[krcranfill]

So I am struggling with finding a way to implement a copy/paste scheme where I can assign the value of a filtered range using something similar to:

 set copyrng = worksheets("Sheet1").listobjects("table1").listcolumns(1).databodyrange 
set paste_rng = worksheets("Sheet2").range("A1:A"& copyrng.rows.count)
paste_rng.value2 = copyrng.value2

but I am running into a difficulty with selecting only the filtered cells in my first line where I set the copyrng object. It is grabbing the entire (unfiltered) range and I need to find a way to assign it to only visible cells in the databodyrange. Is that possible?

 

[Fluff]

You cannot do it like, try

 Set copyRng = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).DataBodyRange.SpecialCells(xlVisible)
Set paste_rng = Worksheets("Sheet2").Range("A1")
copyRng.Copy
paste_rng.PasteSpecial xlPasteValues

 

[krcranfill]

Ok, so thats ALMOST working.....but for some reason I can't seem to wrap my head around, if I add a line to just select the copy range and then step through the code line by line, when I get to the "copyrng.select" line, the correct range of 1632 items is selected, but then when I step to the next line and it counts the cells in the range only up to the point where the data meets the first criteria of the filter (the Y2 criteria) and returns a value of 1187 for Dest_rng_len. I mean I can always solve this by filtering twice and copying twice, but I really want to try to minimize copy/paste functions as much as possible for the sake of computational speed. Any idea why the selection is capturing the entire (filtered) data set but the .rows.count and the paste functions are only grabbing the first half of the criteria? (As a point of interest, the results from each criteria are in a contiguous block of rows but the two blocks are not contiguous with each other in case thats helpful for troubleshooting)

Sub Import_to_Group_Sheets()

    Worksheets("Resource Group Table").Range("Jobs_List_Res_Grp").AutoFilter Field:=1, Criteria1:=Worksheets("SA").Range("Y2").Value2, Operator:=xlOr, Criteria2:=Worksheets("SA").Range("Y3"), Operator:=xlFilterValues
        'filter SA group operations
    Dim Dest_rng_len As Integer
    Set copyrng = Worksheets("Resource Group Table").ListObjects("Jobs_List_Res_Grp").ListColumns(8).DataBodyRange.SpecialCells(xlVisible)
    copyrng.Select
    Dest_rng_len = copyrng.Rows.Count
    Worksheets("SA").ListObjects("SA_Breakdown").Resize Range("B1:U" & Dest_rng_len + 1)
Set paste_rng = Worksheets("SA").ListObjects("Jobs_List_Res_Grp").ListColumns(1).DataBodyRange
copyrng.Copy
paste_rng.PasteSpecial xlPasteValues

    
    
End Sub

 

[Fluff]

You cannot use .Rows.Count on a non-contiguous range, as it will only count the 1st contiguous range.

 

[krcranfill]

You cannot use .Rows.Count on a non-contiguous range, as it will only count the 1st contiguous range.

Any thoughts on a single way to grab the length of the complete set then?

 

[Fluff]

Yes, as I showed in post#2

 

[krcranfill]

Yes, as I showed in post#2

Haha, disappointing, but fair enough. Can't always get what we want ehh?

 

[Fluff]

Why do you feel the need to find how many rows there are?

 

[krcranfill]

Why do you feel the need to find how many rows there are?

so that I can resize the table into which it is being copied before the copy

 

[BSALV]

copy from listobject to listobject (but i'm not sure what you copy to where)

Sub Import_to_Group_Sheets()

     With Worksheets("Resource Group Table").ListObjects("Jobs_List_Res_Grp")
          .Range.AutoFilter Field:=1, Criteria1:=Worksheets("SA").Range("Y2").Value2, Operator:=xlOr, Criteria2:=Worksheets("SA").Range("Y3"), Operator:=xlFilterValues     'filter SA group operations
          .ListColumns(8).DataBodyRange.SpecialCells(xlVisible).Copy
     End With
     Worksheets("SA").ListObjects("SA_Breakdown").ListRows.Add.Range.Range("A1").PasteSpecial xlPasteValues
End Sub