# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  [SOLVED] Object doesn't support this property or method

## davegb

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.

----------


## Jim Thomlinson

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

----------


## davegb

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

----------


## Tom Ogilvy

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" <JimThomlinson@discussions.microsoft.com> wrote in message
news:B6973221-9B93-465E-867D-37A0BF205D6C@microsoft.com...
> 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.
> >
> >

----------


## Darrin Henshaw

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

----------


## Jim Thomlinson

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" <JimThomlinson@discussions.microsoft.com> wrote in message
> news:B6973221-9B93-465E-867D-37A0BF205D6C@microsoft.com...
> > 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.
> > >
> > >
>
>
>

----------


## davegb

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

----------


## Tom Ogilvy

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" <davegb@safebrowse.com> wrote in message
news:1115752328.614453.300770@o13g2000cwo.googlegroups.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.
>

----------


## davegb

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.

----------


## davegb

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 Ogilvy

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" <davegb@safebrowse.com> wrote in message
news:1115761938.086608.250840@f14g2000cwb.googlegroups.com...
> 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!
>

----------


## davegb

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

----------


## vicky_mohite

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

----------

