Hi,
I have a macro below which is copy and pasting cells from another worksheet.
I'm trying to update it so it will do paste special. if I use PasteSpecial xlPasteValues at the end of each row I am getting an Compile Error: Expected:end of statement error message.
Below is the full script.
How would I be able to change it so it would do paste special values?
Thanks.
Private Sub Copy_Existing_Asset_Click()
Dim i As Long
Dim iLastRow As Long
Dim iTarget As Long
With Worksheets("fleet")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "E").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "N").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "O").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "P").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "Q").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "R").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
With Worksheets("Property")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "G").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "R").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "S").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "T").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "U").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "V").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
With Worksheets("Other Capex")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "E").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "N").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "O").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "P").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "Q").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "R").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
MsgBox ("Existing Asset Copy & Paste Complete")
End Sub
I have a macro below which is copy and pasting cells from another worksheet.
I'm trying to update it so it will do paste special. if I use PasteSpecial xlPasteValues at the end of each row I am getting an Compile Error: Expected:end of statement error message.
Below is the full script.
How would I be able to change it so it would do paste special values?
Thanks.
Private Sub Copy_Existing_Asset_Click()
Dim i As Long
Dim iLastRow As Long
Dim iTarget As Long
With Worksheets("fleet")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "E").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "N").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "O").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "P").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "Q").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "R").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
With Worksheets("Property")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "G").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "R").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "S").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "T").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "U").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "V").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
With Worksheets("Other Capex")
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value = "Capex" Then
iTarget = iTarget + 1
.Cells(i, "B").Copy Worksheets("Sheet7").Range("A" & iTarget + 1)
.Cells(i, "C").Copy Worksheets("Sheet7").Range("B" & iTarget + 1)
.Cells(i, "D").Copy Worksheets("Sheet7").Range("C" & iTarget + 1)
.Cells(i, "E").Copy Worksheets("Sheet7").Range("D" & iTarget + 1)
.Cells(i, "F").Copy Worksheets("Sheet7").Range("F" & iTarget + 1)
Worksheets("Sheet7").Range("G" & iTarget + 1).Value = "Expenditure"
Worksheets("Sheet7").Range("H" & iTarget + 1).Value = "Depn & Loss on Sale"
Worksheets("Sheet7").Range("I" & iTarget + 1).Value = "2.62"
Worksheets("Sheet7").Range("J" & iTarget + 1).Value = "Depreciation Expense"
.Cells(i, "N").Copy Worksheets("Sheet7").Range("K" & iTarget + 1)
.Cells(i, "O").Copy Worksheets("Sheet7").Range("L" & iTarget + 1)
.Cells(i, "P").Copy Worksheets("Sheet7").Range("M" & iTarget + 1)
.Cells(i, "Q").Copy Worksheets("Sheet7").Range("N" & iTarget + 1)
.Cells(i, "R").Copy Worksheets("Sheet7").Range("O" & iTarget + 1)
End If
Next i
End With
MsgBox ("Existing Asset Copy & Paste Complete")
End Sub