PDA

View Full Version : PasteSpecial



maninjapan
05-18-2012, 11:28 AM
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


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

Rob342
05-18-2012, 01:13 PM
Try

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


Rob

sassora
05-18-2012, 11:49 PM
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:

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

Bob Phillips
05-19-2012, 01:58 AM
Resize can also help to make the code look much tidier

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

maninjapan
05-21-2012, 10:22 AM
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?
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

Thank you again

Bob Phillips
05-21-2012, 10:44 AM
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