title: Excel VBA - copying and pasting visible table rows only
body: <p>I am trying to copy only the visible rows in a table into a seperate worksheet in the same workbook. I'm a little new to using the 'ListObject' approach to dealing with tables (for a few reasons, referencing the table directly is a better approach here in terms of the rest of my module)</p>
  
  <p>Below is my best attempt, when I run it I get 'run-time error '438'' on the <code>'Sheets("Sheet8").Range("A1").Paste'</code> line, I've been scouring the internet for an hour now trying to figure out what I'm doing wrong, how do I need to re-phrase it so that it pastes the copied data into another sheet/table? Any assistance would be appreciated!</p>
  
  <p>Thanks,</p>
  
  <p>Adam</p>
  
  <pre><code>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: " &amp; TableName)
      Resume finally
  
  End Sub
  </code></pre>