[SOLVED] Object doesn't support this property or method

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >
> >

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 ***

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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.

I found one problem. Near the top of the code, I replaced the lines:

Range("A195:A199").Select
Selection.Copy

With:

WkSht.Range("A195:A199").Copy

I think I was copying blank cells and that was causing part of the
problem. But I'm still getting an error "PasteSpecial of Range class
failed". I'm not sure I'm copying the cells I want. How can I check to
see what's being copied? I've stepped through the macro but that
doesn't help. It would be rather tedious but I could set each of the 4
cells I want to copy to a variable, then paste the 4 values in below
the filtered data. But I'm sure there's an easier way.
Thanks to all both of you for your patience!

I suspect by the time you get to the paste command, the clipboard is empty
(empty cells shouldn't make any difference if they are in the clipboard -
but I don't think there is anything in the clipboard at the point you
paste).

You should do your copy and paste in one command. Replace your current
paste line with

WkSht.Range("A195:A199").Copy Destination:= _
Worksheets("County Records").Range("a5") _
.End(xlDown).Offset(2,0)

You can still copy from a protected sheet, so that shouldn't be of concern.

--
Regards,
Tom Ogilvy



"davegb" <[email protected]> wrote in message
news:[email protected]...
> I found one problem. Near the top of the code, I replaced the lines:
>
> Range("A195:A199").Select
> Selection.Copy
>
> With:
>
> WkSht.Range("A195:A199").Copy
>
> I think I was copying blank cells and that was causing part of the
> problem. But I'm still getting an error "PasteSpecial of Range class
> failed". I'm not sure I'm copying the cells I want. How can I check to
> see what's being copied? I've stepped through the macro but that
> doesn't help. It would be rather tedious but I could set each of the 4
> cells I want to copy to a variable, then paste the 4 values in below
> the filtered data. But I'm sure there's an easier way.
> Thanks to all both of you for your patience!
>

Tom,
Thanks again. That must have been the problem, it's works great with
your line of code. And thanks for the tip about doing the copy and
paste in a single step so the clipboard has what I want on it!
Dave

"Run time error 438 object doesn't support this property sheet"
give the solution