I am trying to copy only visible rows in a table into a separate worksheet in the same book. I'm a little new to using the ListObject approach for working with tables (for several reasons, referencing a table directly is the best approach to the rest of my module)
Below is my best attempt, when I run it, I get a "run-time error" 438 'error in the line 'Sheets("Sheet8").Range("A1").Paste' , I have been combing the Internet for an hour, trying to figure out what I'm doing wrong, how do I need to rephrase it so that it pastes the copied data into another sheet / table? Any help would be appreciated!
Thanks,
Adam
Private Sub CopyVisibleAreaOfTable(ByVal TableName As String) Const FN_NAME As String = "CopyVisibleAreaOfTable" On Error GoTo catch Dim TargetTable As ListObject Dim NumberOfAreas As Long Set TargetTable = Sheets("Adj1").ListObjects(TableName) ' Check that there are fewer than 8192 seperate areas With TargetTable.ListColumns(1).Range NumberOfAreas = .SpecialCells(xlCellTypeVisible).Areas(1).Cells.Count Debug.Print NumberOfAreas End With If NumberOfAreas = 0 Then 'Do something to trigger an error message Else TargetTable.Range.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet8").Range("A1").Paste Application.CutCopyMode = False End If finally: Exit Sub catch: Call ErrorReport(FN_NAME, True, Err.Number, Err.Description, "Table Name: " & TableName) Resume finally End Sub
source share