使用相对引用在 VBA 中复制和粘贴？（错误代码 1004）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25385924/
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
Copy and Paste in VBA using relative references? (Error Code 1004)
提问by
New to this forum so sorry if this is off. I'm trying to do a simple copying of cell values from one worksheet in a book to another worksheet, but need to use relative cell references as the number of rows that will be copy/pasted changes depending on the data inputted.
这个论坛的新手很抱歉，如果这是关闭的。我正在尝试将单元格值从书中的一个工作表简单复制到另一个工作表，但需要使用相对单元格引用，因为将根据输入的数据复制/粘贴的行数发生变化。
The (very simple) code so far is:
到目前为止的（非常简单的）代码是：
Sub SuitorList()
'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value
'Copying Statistics
Sheets("Charts").Range(Cells(1, 1), Cells(Row, 1)).Value = _
Sheets("Data").Range(Cells(1, 1), Cells(Row, 1)).Value
End Sub
This code works fine when I use absolute cell references (i.e. "B1:B7") but when I use a relative reference I receive error code 1004: Application-defined or object-defined error.
当我使用绝对单元格引用（即“B1:B7”）时，此代码工作正常，但是当我使用相对引用时，我收到错误代码 1004：应用程序定义或对象定义错误。
Any thoughts?
有什么想法吗？
采纳答案by Jared
Thanks for the help. I was able to find a work around using the following code:
谢谢您的帮助。我能够使用以下代码找到解决方法：
Sub SuitorList()
'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value
'Copying Statistics
For i = 1 To Row
Sheets("Charts").Range("A" & i).Value = Sheets("Data").Range("A" & i).Value
Next
End Sub
回答by evenprime
Alternative Solution:
替代解决方案：
If you are not a fan of Loops, use Worksheet.Cells Property
如果您不喜欢Loops，请使用Worksheet.Cells 属性
Sub SuitorList()
'Defining Variables
Dim Row As Integer
Set wd = ThisWorkbook.Worksheets("Data")
Set wc = ThisWorkbook.Worksheets("Charts")
Row = Sheets("References").Cells(6, 2).Value
'Copying Statistics
Range(wd.Cells(1, 1), wd.Cells(Row, 1)).Copy Destination:=Range(wc.Cells(1, 1), wc.Cells(Row, 1))
End Sub
回答by Jared
If you are copying data from one sheet to another and the amount of data to be copied/pasted is always changing then I would do something like this. Which is filtering the data from your selection sheet then copying it and pasting it to your destination sheet by finding the first blank cell. You may have to mess with this a bit, but it is a good start.
如果您将数据从一张纸复制到另一张纸，并且要复制/粘贴的数据量总是在变化，那么我会做这样的事情。这是从您的选择表中过滤数据，然后通过找到第一个空白单元格将其复制并粘贴到目标表中。您可能不得不对此稍作处理，但这是一个好的开始。
'Defining Variables
Dim Row As Integer
Row = Sheets("References").Cells(6, 2).Value
'switches the sheet
Sheets("Charts").Select
'filters a table based on the value of the Row variable
ActiveSheet.ListObjects("Table1").range.AutoFilter Field:=1, Criteria1:= _
range("Row"), Operator:=xlAnd
'moves to the first cell in the filtered range
range("A1").Select
'selects all values in the range and copies to clipboard
range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
'switches the sheet back to data sheet
Sheets("Data").Select
'finds the first blank cell in the declared range you want to paste into
ActiveSheet.range("A:A").Find("").Select
'pastes the selection
ActiveSheet.Paste
