以下代码是复制和粘贴从sheet1到sheet2的行,具体取决于第一个工作表中E列中满足的关键字条件 .
在代码行5到10之间,我试图将整个第十行复制到sheet2,以用作此工作表的列 Headers ,其中将创建一个数据透视表 .
每当我尝试运行任何操作来复制和粘贴此行时,我都会收到“Applicated-defined或Object-defined error” .
有人可以帮忙吗?
Sub mileStoneDateChanger()
Dim r As Long, pasteRowIndex As Long, v() As Long, i As Long
Dim lastRow As Long
Dim lCol As Long
Sheets("Sheet1").Select
Rows("10:10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste
lastRow = Cells.Find(What:="*",
After:=Range("A1"),
LookAt:=xlPart,
LookIn:=xlFormulas,
SearchOrder:=xlByRows,
SearchDirection:=xlPrevious,
MatchCase:=False).Row
MsgBox "Last Row: " & lastRow
pasteRowIndex = 1
With Sheets("Sheet1")
For r = 1 To lastRow
If .Cells(r, "E").Value Like "Milestone*" Then
If UBound(Split(.Cells(r, "E"), ",")) > 0 Then
i = i + 1
ReDim v(1 To i)
v(i) = pasteRowIndex
End If
Sheets("Sheet1").Rows(r).Copy Sheets("Sheet2").Rows(pasteRowIndex)
pasteRowIndex = pasteRowIndex + 1
End If
Next r
End With
With Sheets("Sheet2")
newLastRow = pasteRowIndex
If IsArray(v) Then
.Columns(6).Insert shift:=xlToRight
For i = 1 To newLastRow
If InStr(1, .Cells(i, "E"), ",") Then
.Cells(i, "F").Value = Split(.Cells(i, "E"), ",")(1)
End If
Next i
End If
End With
End Sub
3 回答
这是Sheet2中Sheet1到A1中第10行的副本
将
Selection.Paste
替换为Selection.PasteSpecial
Range("A1")
无法粘贴一整行 . 因此,请使用以下内容:免责声明 - 您真的不想在VBA中使用
Select
和Active
. How to avoid using Select in Excel VBA最好的情况可能是这样避免
Copy
: