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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.