I'm trying to paste some text from one worksheet to another, but I keep
getting the error "Object doesn't support this property or method". I
checked in the object browser and it shows range, offset and paste as
belonging to worksheets.
I'm trying to paste the contents of the clipboard into cells starting 2
rows below the last cell with data in column a on the "County Records"
worksheet.

Worksheets("County Records").Range("a5").xlDown.Offset(2, 0).Paste

I know some of you will spot this in a second. Please let me know what
I'm doing wrong, and why this doesn't work even though the object
browser tells me they all "fit".
Thanks in advance.

xlDown is an argument of the .end property. This should work

Worksheets("County Records").Range("a5").End(xlDown).Offset(2, 0).Paste

HTH

"davegb" wrote:

I'm trying to paste some text from one worksheet to another, but I keep
getting the error "Object doesn't support this property or method". I
checked in the object browser and it shows range, offset and paste as
belonging to worksheets.
I'm trying to paste the contents of the clipboard into cells starting 2
rows below the last cell with data in column a on the "County Records"
worksheet.

Worksheets("County Records").Range("a5").xlDown.Offset(2, 0).Paste

I know some of you will spot this in a second. Please let me know what
I'm doing wrong, and why this doesn't work even though the object
browser tells me they all "fit".
Thanks in advance.

Thanks, Jim. I changed it and now I'm getting "Application defined or
object defined error" on the same line. Any ideas?

Actually, a range doesn't have a paste method. So if you want this format
you need:

Worksheets("County Records").Range("a5").End( _
xlDown).Offset(2, 0).Pastespecial xlPasteAll

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
xlDown is an argument of the .end property. This should work

Worksheets("County Records").Range("a5").End(xlDown).Offset(2, 0).Paste

HTH

"davegb" wrote:

I'm trying to paste some text from one worksheet to another, but I keep
getting the error "Object doesn't support this property or method". I
checked in the object browser and it shows range, offset and paste as
belonging to worksheets.
I'm trying to paste the contents of the clipboard into cells starting 2
rows below the last cell with data in column a on the "County Records"
worksheet.

Worksheets("County Records").Range("a5").xlDown.Offset(2, 0).Paste

I know some of you will spot this in a second. Please let me know what
I'm doing wrong, and why this doesn't work even though the object
browser tells me they all "fit".
Thanks in advance.

Good point... Thanks Tom. I always just use the range object as the argument
of the copy function. Like I have always said... You don't miss much...

Sheet1.Range("A1:A10").Copy Sheet2.Range("B1")

or in this case

Range("XXX").Copy Worksheets("County
Records").Range("a5").End(xlDown).Offset(2, 0)

Thanks...

"Tom Ogilvy" wrote:

Actually, a range doesn't have a paste method. So if you want this format
you need:

Worksheets("County Records").Range("a5").End( _
xlDown).Offset(2, 0).Pastespecial xlPasteAll

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in message
...
xlDown is an argument of the .end property. This should work

Worksheets("County Records").Range("a5").End(xlDown).Offset(2, 0).Paste

HTH

"davegb" wrote:

I'm trying to paste some text from one worksheet to another, but I keep
getting the error "Object doesn't support this property or method". I
checked in the object browser and it shows range, offset and paste as
belonging to worksheets.
I'm trying to paste the contents of the clipboard into cells starting 2
rows below the last cell with data in column a on the "County Records"
worksheet.

Worksheets("County Records").Range("a5").xlDown.Offset(2, 0).Paste

I know some of you will spot this in a second. Please let me know what
I'm doing wrong, and why this doesn't work even though the object
browser tells me they all "fit".
Thanks in advance.

Tom, thanks for the help. But I'm still getting the "Object doesn't
support this property or method" error with the new code.

Worked fine for me when I tested it before posting. Maybe you have a typo.

Here it is again, copied out of the immediate window after executing
successfully:

Worksheets("County Records").Range("a5").End( _
xlDown).Offset(2, 0).Pastespecial xlPasteAll

If there are any hyphen's in the code, this could be a bug in google (don't
know where you are reading this). There should be nothing but letters,
numbers and a few spaces in the code.


--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...
Tom, thanks for the help. But I'm still getting the "Object doesn't
support this property or method" error with the new code.

Strange. I copied your latest one and pasted it into my code just below
the existing one. They match perfectly (I had removed the Google
hyphens before). Now I'm getting "Error 1004: Application defined or
object defined error", both when I run the whole program and when I run
it in the Immediate Window. The entire program is:

Sub RecurExtract()
'Password used
Dim CtyCode As String
Dim WkSht As Object
Dim PWORD As String
Dim ChPrior, PostServ, FirstDt, LastDt As String

PWORD = "dave"

CtyCode = ActiveCell
Set WkSht = ActiveWorkbook.Sheets("Recurrence Records")
WkSht.Unprotect Password:=PWORD
Sheets("Recurrence Records").Range("S2") = CtyCode
Range("A195:A199").Select
Selection.Copy

WkSht.Protect Password:=PWORD

Sheets("County Records").Select
Worksheets("County Records").UsedRange.Clear

Range("a1:i1").Select
Selection.Merge
ActiveCell.FormulaR1C1 = _
"WARNING: This data will be erased the next time County
Records are extracted. "
With ActiveCell.Characters(Start:=1, Length:=78).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.ColorIndex = 7
End With

Range("A2:I2").Select
Selection.Merge
ActiveCell.FormulaR1C1 = _
"If you wish to save the data, copy and paste it to another
spreadsheet or print it out before doing another data extraction."
With ActiveCell.Characters(Start:=1, Length:=124).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 7
End With
Range("A2:I2").Select
With Selection
.WrapText = True
.MergeCells = True
End With

Rows("2:2").RowHeight = 30

Application.CutCopyMode = False
Sheets("Recurrence Records").Range("A1:M192").AdvancedFilter
Action:= _
xlFilterCopy, CriteriaRange:=Sheets("Recurrence
Records").Range("S1:S2"), _
CopyToRange:=Range("A5"), Unique:=False
Range("A4:E4").Select
Selection.Merge
Range("a4") = CtyCode & " County Recurrence Records"
With ActiveCell.Characters(Start:=1, Length:=78).Font
.Name = "Arial"
.FontStyle = "Bold"
End With
Columns("A:M").EntireColumn.AutoFit

Range("A5:M5").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True

End With
Rows("5:5").RowHeight = 24.75


Worksheets("County Records").Range("a5").End(xlDown).Offset(2,
0).PasteSpecial xlPasteAll

End Sub

How can it do one thing in your Immediate Window and something else in
mine?
Thanks for all the help.

Trying that in the Immediate Window gave me the same error. However,
this did work:

Worksheets("Country Records").Range("a5").Select
With ActiveCell
.End(xlDown).Select
.Offset(2, 0).Select
End With



*** Sent via Developersdex http://www.developersdex.com ***