Excel VBA - 仅复制和粘贴可见的表格行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16039774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - copying and pasting visible table rows only
提问by AdamDynamic
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)
我试图仅将表中的可见行复制到同一工作簿中的单独工作表中。我对使用“ListObject”方法处理表格有点陌生（出于一些原因，就我的模块的其余部分而言，直接引用表格是一种更好的方法）
Below is my best attempt, when I run it I get 'run-time error '438'' on the 'Sheets("Sheet8").Range("A1").Paste'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!
下面是我最好的尝试，当我运行它时，我在线上收到“运行时错误‘438’” 'Sheets("Sheet8").Range("A1").Paste'，我已经在互联网上搜索了一个小时，试图找出我做错了什么，我该怎么做需要重新措辞，以便将复制的数据粘贴到另一个工作表/表格中？任何援助将不胜感激！
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
回答by David Zemens
Specify the destination as part of the .Copymethod:
指定目标作为.Copy方法的一部分：
TargetTable.Range.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet8").Range("A1")
