PDA

View Full Version : error 1004 unable to get pastespecial property of range class



rrosa1
06-29-2015, 06:31 AM
hi i have following code it give this error can u pl help how to use paste special hear?



ws.Range(rTFind.Offset(-1), rBFind.Offset(-2)).EntireRow.Copy _
Sheets(sname).Cells(rTitle + 1, "A").PasteSpecial.x1Value

SamT
06-29-2015, 06:51 AM
Remove the Line Continuation underscore on the first line

Aflatoon
06-29-2015, 08:29 AM
The second line should also read:

Sheets(sname).Cells(rTitle + 1, "A").PasteSpecial xlPasteValues

(note the constant starts with XL and not X1)

rrosa1
06-29-2015, 09:58 AM
i did try that but still giving error


ws.Range(rTFind.Offset(-1), rBFind.Offset(-2)).EntireRow.Copy
Sheets(sname).Cells(rTitle + 1, "A").PasteSpecial xlPastevalues

snb
06-29-2015, 10:22 AM
Partial information, partial answers,,,,




Sheets(sname).Cells(rTitle + 1, "A").entirerow.resize(2)=rTFind.Offset(-2).resize(2).entirerow.value

rrosa1
06-29-2015, 01:11 PM
thanks snb here is my do loop



Do
Set rBFind = ws.Range("A:A").FindNext(rTFind)
sname = Format(Day(Int(rTFind.Offset(-1))) + 1, "DD")
If Not Evaluate("=ISREF('" & sname & "'!A1)") Then 'create sheet if needed
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sname
Else 'clear sheet if it exists
Sheets(sname).Move After:=Sheets(Sheets.Count)
Sheets(sname).Cells.Clear
End If
ws.Rows(rTitle).Copy Sheets(sname).Cells(rTitle, "A")
If rBFind.Address <> rFirst.Address Then
'ws.Range(rTFind.Offset(-1), rBFind.Offset(-2)).EntireRow.Copy _
Sheets(sname).Cells(rTitle + 1, "A")'.PasteSpecial 'xlPastevalues
ws.Range(rTFind.Offset(-1), rBFind.Offset(-2)).EntireRow.Copy



Sheets(sname).Range(Cells(rTitle + 1, "A")).PasteSpecial xlPasteValuesAndNumberFormats
''' error 1004 at last line now ""application define or object define error
loop

snb
06-29-2015, 01:31 PM
You'd better use autofilter than find...findnext


Sub M_snb()
with sheet1.columns(1)
.autofilter 1, "searchitem"
for each cl in .specialcells(12)
if cl.row>1 then
' etc
end if
next
end with
End Sub