VBA - Copy Visible cells not selecting everything

Question

I have a very long macro that I've written that looks at a report sheet, breaks it down by many different criteria (putting some of the results on different sheets), and does hundreds of calculations. Everything was looking good until I noticed that when the result was one line, it wasn't calculating properly. I traced it back to this block. (The top two lines are workarounds to the .showalldata method not working properly.)

Worksheets("900").ListObjects("NF900T").ShowAutoFilter = False
Worksheets("900").ListObjects("NF900T").ShowAutoFilter = True
sh900.Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, shNFTeen.Range("GC63").CurrentRegion
shNFSnap.Range("C109") = "=subtotal(3,NF900T[BID])"
shNFSnap.Range("E109") = "=subtotal(9,NF900T[CIRC])"
sh900.Range("A1").SpecialCells(xlCellTypeVisible).Copy
shScratch.Range("A1").PasteSpecial xlPasteValues

Everything works fine until it gets to the 6th line where it needs to copy the visible cells. (This particular block doesn't have an autofilter but some blocks do, so I had to use the visible cells property to not include any filtered results on those blocks.) When it runs this line, it only selects/copies A1 even though there are other headers and filled-in cells. It seems to work fine (select and copy everything) when there is more than one line of results.

Also, I used sh900.Range[...] but should I be referencing that table instead of the sheet and range? Or is that a six in one hand, half a dozen in the other situation and doesn't matter?

Answer 1

I'm not sure I fully understand your question as stated, but supposing you are trying to select the header and visible cells of a ListObject called NF900T which may have filters applied, then work with the ListObject itself.

Copy the header and paste into the new sheet. Then copy the visible cells of the DataBodyRange and paste beneath the pasted header.

Dim lo As ListObject
Set lo = Worksheets("900").ListObjects("NF900T")

' (Clear the target area in wsScratch if needed)

lo.HeaderRowRange.Copy
wsScratch.Range("A1").PasteSpecial xlPasteValues


' Copy only the visible cells within the data body range
lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

' Paste the copied data body range below the header in wsScratch
wsScratch.Range("A2").PasteSpecial xlPasteValues

Application.CutCopyMode = False