vba 如何从过滤范围复制粘贴特殊单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17294154/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-11 21:51:18 来源:igfitidea点击:
How to Copy-PasteSpecial cells from filtered range
提问by Carissa De Vera
Here's my code below.
下面是我的代码。
Sub AddExistingItemToRWP()
Dim AddRow As Integer
Dim eLastRow As Integer
AddRow = Worksheets("Recipe Workarea-Product").Range("A" & Rows.Count).End(xlUp).Row
eLastRow = Worksheets("Additional Existing Raw Mat.").Range("A" & Rows.Count).End(xlUp).Row
Dim Rng As Range
Sheets("Additional Existing Raw Mat.").Select
Set Rng = ActiveSheet.AutoFilter.Range
With Sheet12
With .Range("$A:K" & eLastRow)
.AutoFilter Field:=11, Criteria1:=("Y")
.SpecialCells (xlCellTypeVisible)
.Offset(1, 0) _
.Copy Destination:=Sheet8.Range("H" & AddRow + 1)
.PasteSpecial Paste = xlPasteValues
End With
End With
AutoFillCols (AddRow)
Sheets("Additional Existing Raw Mat.").Select
End Sub
The .pastespecial cells seems to be not working. What is the correct syntax for this?
.pastespecial 单元格似乎不起作用。什么是正确的语法？
采纳答案by chris neilsen
Four things:
四件事：
.SpecialCells(xlCellTypeVisible)returns a reference to a range, but you don't use it- You can't use both
Destination:= ...and.PasteSpecialwith oneCopy. Choose one. - You mean
.PasteSpecial Paste:=xlPasteValuesnot.PasteSpecial Paste = xlPasteValues - You activate and filter sheet
"Additional Existing Raw Mat.", then refer to a filter onSheet12. Are you sure thats right?
.SpecialCells(xlCellTypeVisible)返回对范围的引用，但您不使用它- 您不能使用这两个
Destination:= ...和.PasteSpecial一个Copy。选一个。 - 你的意思
.PasteSpecial Paste:=xlPasteValues不是.PasteSpecial Paste = xlPasteValues - 您激活并过滤表
"Additional Existing Raw Mat."，然后在 上引用过滤器Sheet12。你确定那是对的？
Update:How to use Copy PasteSpecial
更新：如何使用 Copy PasteSpecial
.Copy
Sheet8.Range("H" & AddRow + 1).PasteSpecial Paste:=xlPasteValues
回答by Carissa De Vera
I finally got the solution to my problem. Here's my code:
我终于得到了解决我的问题的方法。这是我的代码：
Sub AddExistingItemToRWP()
Dim AddRow As Integer
Dim eLastRow As Integer
AddRow = Worksheets("Recipe Workarea-Product").Range("A" & Rows.Count).End(xlUp).Row
eLastRow = Worksheets("Additional Existing Raw Mat.").Range("A" & Rows.Count).End(xlUp).Row
Dim Rng As Range
Sheets("Additional Existing Raw Mat.").Select
Set Rng = ActiveSheet.AutoFilter.Range
With Sheet12
With .Range("$A:K" & eLastRow)
.AutoFilter Field:=11, Criteria1:=("Y")
.SpecialCells(xlCellTypeVisible).Select
Selection.Offset(1, 0).Copy
Sheets("Recipe Workarea-Product").Select
Range("H" & AddRow + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
End With
End With
AutoFillCols (AddRow)
Sheets("Additional Existing Raw Mat.").Select
End Sub
