对象必需在两张纸之间复制和粘贴时出错(Object Required Error for copy and paste between 2 sheets)

对象必需在两张纸之间复制和粘贴时出错(Object Required Error for copy and paste between 2 sheets)

我是excel VBA的新手，通过书籍，试用，论坛和实验学到了我所知道的一切（换句话说，不要对我的代码大笑）。

我编写此代码来复制来自当前“客户端工作表”的两（2）个连续单元格中的值（此代码将通过放置在通过另一个宏操作创建的同一工作簿中的许多客户端工作表中的按钮进行初始化）。 然后将复制的值粘贴到固定工作表（2），其中当前工作表行中的唯一值与工作表（2）中行中的唯一值匹配，但偏离工作表中唯一值单元格的-7列（2）对应的行。

我得到一个Object Required - 错误424.在“ ActiveCell.PasteSpecial.xlValues ”行 请帮忙。 花几个小时。

我附上了以下代码

Sub UpdateSht2() Dim w1 As Worksheet, w2 As Worksheet Dim c As String, d As Range, e As Range Set w1 = ActiveWorkbook.ActiveSheet Set w2 = Sheets(2) Set d = ActiveCell Set e = w2.Range("L2:L5000") ActiveWorkbook.ActiveSheet.Activate c = ActiveCell.Offset(0, 3).value Cells.Range(d.Offset(0, -1), d).Copy w2.Activate w2.Unprotect e.Select e.Find(c, LookIn:=xlValues).Select ActiveCell.Offset(0, -7).Activate ActiveCell.PasteSpecial.xlValues w2.Protect w1.Activate d.Select End Sub

I am new at excel VBA and have learnt all I know through books, trial, forums, and experimentation (in otherwords dont laugh too much at my code).

I wrote this code to copy the values from a range of two (2) contigous cells from a current "client sheet" (this code will initialize through a button placed in many client sheets in the same workbook created through another macro operation). The copied values will then be pasted to a fixed sheet(2) where a unique value in the row of current sheet matches the unique value in the row in sheet(2), but offset -7 columns from the unique value cell in the Sheet(2) corresponding row.

I am getting a Object Required - error 424. at the line "ActiveCell.PasteSpecial.xlValues" Please help. Spend hours and hours.

I have attached the code below

Sub UpdateSht2() Dim w1 As Worksheet, w2 As Worksheet Dim c As String, d As Range, e As Range Set w1 = ActiveWorkbook.ActiveSheet Set w2 = Sheets(2) Set d = ActiveCell Set e = w2.Range("L2:L5000") ActiveWorkbook.ActiveSheet.Activate c = ActiveCell.Offset(0, 3).value Cells.Range(d.Offset(0, -1), d).Copy w2.Activate w2.Unprotect e.Select e.Find(c, LookIn:=xlValues).Select ActiveCell.Offset(0, -7).Activate ActiveCell.PasteSpecial.xlValues w2.Protect w1.Activate d.Select End Sub

最满意答案

希望这能帮到你......

Sub UpdateSht2() 'Dim w1 As Worksheet, w2 As Worksheet 'Dim c As String, d As Range, e As Range Dim w1 As Worksheet 'Looks its more code, but it reads easy Dim w2 As Worksheet Dim c As String Dim d As Range Dim e As Range Set w1 = ActiveWorkbook.ActiveSheet 'ok Set w2 = Sheets(2) 'ok Set d = ActiveCell 'ok Set e = w2.Range("L2:L5000") 'ok ActiveWorkbook.ActiveSheet.Activate 'Why you want to ACTIVATE, the ACTIVESHEET? It is ok, but this line does nothing. c = ActiveCell.Offset(0, 3).Value 'Store data string into c var. '########################################## 'Cells.Range(d.Offset(0, -1), d).Copy ' Well... some words here... '########################################## 'cells.range([...]).copy it is completly functional, but is important to keep you code 'clean and easy to read, for you most of all. And then you can use this 'Range([...]).copy 'Inside [...] 'you can put a string with the address of the range you want to use: "A1:D30" 'You can use the Cells functions: 'Range(Cells(1,1),Cells(30,4)).copy and this is the same range as "A1:D30" 'Use cells this way: Cells(Row, Column) 'This is because Range can read that this object (Cells) is a cell, but if you use a range var type you get an error '#########################Fail 'Sub Fail() ' Dim d As Range ' Set d = Range("A1") ' Range(d).Select 'End Sub '######################### '#########################OK 'Sub Good() ' Dim d As Range ' Set d = Range("A1") ' Range(d.Address).Select 'here you are returning the address of A1, as string 'End Sub '#########################OK 'Sub Good() ' Dim d As Range 'unused ' Set d = Range("A1") 'unused ' Range(Cells(1, 1), Cells(1, 1)).Select 'The range funct read you want to select A1 of row1 and column1 ' 'Remember using this always need to set it that way ' 'Cells(),Cells() 'End Sub 'And you can use Cells().copy but only with one cell at the time. 'This is very handy when you need to go across many rows and columns... '######################### 'Sub forExample() 'Dim i 'Dim j ' 'For i = 1 To 30 ' For j = 1 To 10 ' Cells(i, j).Value = "R: " & i & "C: " & j ' Next j 'Next i 'End Sub '######################### Result 'R: 1C: 1 R: 1C: 2 R: 1C: 3 'R: 2C: 1 R: 2C: 2 R: 2C: 3 'R: 3C: 1 R: 3C: 2 R: 3C: 3 'R: 4C: 1 R: 4C: 2 R: 4C: 3.... 'Well returning to your code... 'Cells.Range(d.Offset(0, -1), d).Copy you are wrong because you put an object that range can't use... 'The right one: d.Offset(0, -1).Copy 'here copy just one cell! 'Other example: Dim r1 Dim c1 Dim r2 Dim c2 r1 = d.Offset(0, -1).Row 'this is equal as below: r1 = d.Row 'here just take the row integer of d range c1 = d.Offset(0, -1).Column 'Here just take the column integer of d range. 'the other cells... r2 = d.Row c2 = d.Column Range(Cells(r1, c1), Cells(r2, c2)).Copy 'here you copy a range of cells defined by the offset in r1 and c1 'End of example w2.Activate w2.Unprotect e.Select '####Your code e.Find(c, LookIn:=xlValues).Select 'this is ok... but here is my code... ActiveCell.Offset(0, -7).Activate 'ActiveCell.PasteSpecial.xlValues ' wrong 'Heres is the answer of you question...! ActiveCell.PasteSpecial xlPasteValues 'ok '####My code 'Dim f As Range 'always put declarations of vars above. 'Set f = e.Find(c, LookIn:=xlValues) 'f.Offset(0, 7).PasteSpecial xlPasteValues w2.Protect w1.Activate d.Select 'TIP: Never forget the indentation, it will be easy to read if you used. 'i know would be hard, but always is better to use name variables to say more about the data or object 'you store inside... 'instead of "c" you can use a word(s) to tell you more about the stored data. 'But this is just my coding style, may be you are good with that... Peace! End Sub

Hope this help you...

Sub UpdateSht2() 'Dim w1 As Worksheet, w2 As Worksheet 'Dim c As String, d As Range, e As Range Dim w1 As Worksheet 'Looks its more code, but it reads easy Dim w2 As Worksheet Dim c As String Dim d As Range Dim e As Range Set w1 = ActiveWorkbook.ActiveSheet 'ok Set w2 = Sheets(2) 'ok Set d = ActiveCell 'ok Set e = w2.Range("L2:L5000") 'ok ActiveWorkbook.ActiveSheet.Activate 'Why you want to ACTIVATE, the ACTIVESHEET? It is ok, but this line does nothing. c = ActiveCell.Offset(0, 3).Value 'Store data string into c var. '########################################## 'Cells.Range(d.Offset(0, -1), d).Copy ' Well... some words here... '########################################## 'cells.range([...]).copy it is completly functional, but is important to keep you code 'clean and easy to read, for you most of all. And then you can use this 'Range([...]).copy 'Inside [...] 'you can put a string with the address of the range you want to use: "A1:D30" 'You can use the Cells functions: 'Range(Cells(1,1),Cells(30,4)).copy and this is the same range as "A1:D30" 'Use cells this way: Cells(Row, Column) 'This is because Range can read that this object (Cells) is a cell, but if you use a range var type you get an error '#########################Fail 'Sub Fail() ' Dim d As Range ' Set d = Range("A1") ' Range(d).Select 'End Sub '######################### '#########################OK 'Sub Good() ' Dim d As Range ' Set d = Range("A1") ' Range(d.Address).Select 'here you are returning the address of A1, as string 'End Sub '#########################OK 'Sub Good() ' Dim d As Range 'unused ' Set d = Range("A1") 'unused ' Range(Cells(1, 1), Cells(1, 1)).Select 'The range funct read you want to select A1 of row1 and column1 ' 'Remember using this always need to set it that way ' 'Cells(),Cells() 'End Sub 'And you can use Cells().copy but only with one cell at the time. 'This is very handy when you need to go across many rows and columns... '######################### 'Sub forExample() 'Dim i 'Dim j ' 'For i = 1 To 30 ' For j = 1 To 10 ' Cells(i, j).Value = "R: " & i & "C: " & j ' Next j 'Next i 'End Sub '######################### Result 'R: 1C: 1 R: 1C: 2 R: 1C: 3 'R: 2C: 1 R: 2C: 2 R: 2C: 3 'R: 3C: 1 R: 3C: 2 R: 3C: 3 'R: 4C: 1 R: 4C: 2 R: 4C: 3.... 'Well returning to your code... 'Cells.Range(d.Offset(0, -1), d).Copy you are wrong because you put an object that range can't use... 'The right one: d.Offset(0, -1).Copy 'here copy just one cell! 'Other example: Dim r1 Dim c1 Dim r2 Dim c2 r1 = d.Offset(0, -1).Row 'this is equal as below: r1 = d.Row 'here just take the row integer of d range c1 = d.Offset(0, -1).Column 'Here just take the column integer of d range. 'the other cells... r2 = d.Row c2 = d.Column Range(Cells(r1, c1), Cells(r2, c2)).Copy 'here you copy a range of cells defined by the offset in r1 and c1 'End of example w2.Activate w2.Unprotect e.Select '####Your code e.Find(c, LookIn:=xlValues).Select 'this is ok... but here is my code... ActiveCell.Offset(0, -7).Activate 'ActiveCell.PasteSpecial.xlValues ' wrong 'Heres is the answer of you question...! ActiveCell.PasteSpecial xlPasteValues 'ok '####My code 'Dim f As Range 'always put declarations of vars above. 'Set f = e.Find(c, LookIn:=xlValues) 'f.Offset(0, 7).PasteSpecial xlPasteValues w2.Protect w1.Activate d.Select 'TIP: Never forget the indentation, it will be easy to read if you used. 'i know would be hard, but always is better to use name variables to say more about the data or object 'you store inside... 'instead of "c" you can use a word(s) to tell you more about the stored data. 'But this is just my coding style, may be you are good with that... Peace! End Sub