PasteSpecial

[maninjapan]

I am attempting to use the following code to paste the values only from one sheet to another. However, I am getting a run time error '1004'

I created this just from some examples and not really sure where I am going wrong? Any assistance with this would be much appreciated.

Thank you


[VBA]Sub alarmcopy()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 27 To LR
If Range("J" & i).Value = "FLAG" Then Range("A" & i & ":I" & i).Copy
Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
End Sub[/VBA]

[Rob342]

Try
[VBA]
With Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
[/VBA]

Rob

[sassora]

There's an unclosed IF statement - For every If there should be an End If

Also there when you have multiple actions to perform, you would put it as follows:

[VBA]If Range("J" & i).Value = "FLAG" Then
Range("A" & i & ":I" & i).Copy
Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If [/VBA]

[Bob Phillips]

Resize can also help to make the code look much tidier

[VBA]If Cells(i, "J").Value = "FLAG" Then
Cells(i, "A").Resize(, 9).Copy
Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If [/VBA]

[maninjapan]

Thanks, Works fine now. I have tried to rewrite it so it copies from a specific sheet, not just the active sheet. I tried the following but it didnt seem to work.

Have I made any glaring errors here?
[VBA]Sub alarmcopy_kin()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Worksheets("New")
For i = 27 To LR
If Cells(i, "J").Value = "FLAG" Then
Cells(i, "A").Resize(, 9).Copy
Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next i
End With
End Sub[/VBA]

Thank you again

[Bob Phillips]

[vba]Sub alarmcopy_kin()
Dim LR As Long, i As Long

With Worksheets("New")

LR = .Range("B" & .Rows.Count).End(xlUp).Row
For i = 27 To LR

If .Cells(i, "J").Value = "FLAG" Then

.Cells(i, "A").Resize(, 9).Copy
Worksheets("Old").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next i
End With
End Sub [/vba]