MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Error message on Paste operation

  Asked By: Holly    Date: Oct 26    Category: MS Office    Views: 1984

When I try to copy and paste the data from one sheet to another, I am getting an
error message as below.
"Paste method of worksheet class failed"
Pleast help me to resolve this issue.
Note: special paste comment also getting the same error.



5 Answers Found

Answer #1    Answered By: Logan Bouchard     Answered On: Oct 26

saying: "I tried this and it doesn't work, what's wrong"
is kind-of like saying "it hurts when I breathe, can you fix it?"...
There's really no place to start.. actually there is, but it's all the way at
the beginning.
Open a new workbook.. start writing code... Don't think that's what you

We'll need to see part of your code.

has this code worked before?
Have you stepped through it to see if the steps are working as you intended?

Answer #2    Answered By: Aidyn Smith     Answered On: Oct 26

I have enclosed the file for your reference.
When we open the file the userform will automatically opens, where i can select
the store which i wanted to display. After selecting the store number and
cliking on the View button. I need to sort data  by the selected store number and
it has to be pasted in a seperate working sheet  for easy display.
Here after copying the data from the main sheet and pasting the data into the
working sheet (used paste  special command) getting an error  message.
Could you please lookinto the file and get back to me.

Answer #3    Answered By: Abbad Akhtar     Answered On: Oct 26

I think I've got it.
Rather than just tell you, let me try to explain "why" it's doing what it's not
doing... ??

Have you noticed that if you "manually" copy  and paste  cells,
you first select the cells and hit "ctrl-C" or the menu version.
Excel "highlights" the cells with an animated border.

You then select the destination and hit "ctrl-v" and it pastes the cells.
Notice that the original cells are still "highlighted"?

Now, say you decide to first clear the destination cells.
When you select them and hit "delete", the highlighting of the original cells
and if you try to paste, there's nothing in the copy buffer!

I think the reason is that you're really copying to an Excel "clipboard". not a
Windows clipboard.
I don't think the Excel clipboard is actually storing data  (and formatting, and
fonts, and color, etc...)
I think it stores a REFERENCE... then clears the reference when Excel thinks it
is no longer needed.

This is what you're doing.
You first filter the data and select it.
then you go to the destination sheet, and clear the contents.
this empties the buffer!

Clear the destination first.
You don't have to actually SELECT the sheet.
You can use:


then, be sure to remove anything "extra" from between the copy/paste activity.

you can simplify the macro like:
Public Sub sperecret()
Sheets("working").Visible = True
Selection.AutoFilter Field:=3, Criteria1:=UserForm1.ComboBox1.Text
Sheets("working").Range("A1").PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select 'Changed due to error  if only (1) record is returned
Range(Selection, Selection.End(xlDown)).Select

rcount = Selection.Count

totscr1 = (rcount / 10)
totscr2 = (rcount Mod 10)

If totscr2 <> 0 Then
totscr = totscr1 + 1
totscr = totscr1
End If

Sheets("working").Visible = False
Selection.AutoFilter Field:=2
End Sub

Answer #4    Answered By: Cais Nguyen     Answered On: Oct 26

You are absolutely correct. It¢s working now.

Answer #5    Answered By: Jaspreet Kapoor     Answered On: Oct 26

When I use the For Loop to assign the values to the respective controls (like
labels, textbox etc), how can we increment / decrement the control names. For
for i = 1 to 100
userform1.label1.text = cells(i,1)
userform1.textbox10.text = cells(i,2)
userform1.textbox11.text = cells(i,3)
next i
In the above statement it will be assigned only in label1, textbox10, textbox11,
when I is going to be incremented from 1 to 2 the values of hte controls like
label2, textbox20, textbox21 should be assigned.
Could you please help  me on the same.

Didn't find what you were looking for? Find more on Error message on Paste operation Or get search suggestion and latest updates.