MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Newbie question - paste special

  Asked By: Arland    Date: Feb 10    Category: MS Office    Views: 931

I'm trying to paste values from one worksheet to another. A macro
works fine and generates the code

Private Sub cmdPasteFees_Click()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

When I attach this to a button I generate a 'Run Type Error 13 -
Type mismatch' when I get to Cells("A1").Select. Any thoughts
gratefully accepted. I've spent a couple of hours on this and it is
driving me nuts!



10 Answers Found

Answer #1    Answered By: Frederick Greene     Answered On: Feb 10

You select  a range (Range("A52:E92").Select).
But try to paste  it into a cell (A1).

Since you know the size of your range, I would suggest selecting a range
of similar size in the new spreadsheet to match the range selected.

Answer #2    Answered By: Kelly Bell     Answered On: Feb 10

but I've tried this to no effect.

I wonder if the problem is that I'm not clarifying which page I'm copying
from and/or pasting onto. As I say, the code works fine  as a macro. I forgot
to mention in my previous post that the button  I'm attaching it to is on the
sheet I'm copying from.

Answer #3    Answered By: Angel Harris     Answered On: Feb 10

I do not have time to read all the messages to see if you have got the answer
but the problem in the line Cells("A1").Select is that the expression cells
should be written like cells(rowIndex, colIndex) If you want to use the name A1
you should write it as Range("A1") and not as Cells("A1")
Im suprised that the macro wrote it like that

Answer #4    Answered By: Cheri Garcia     Answered On: Feb 10

Try the following (only minor changes to your code) - I haven't tried it
attached it to a button, but it works as a macro.

Private Sub cmdPasteFees_Click()
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Answer #5    Answered By: Julian Long     Answered On: Feb 10

but the solutions you suggest both give 'error 1004 Application
defined or object defined error'.

I'm confused as my original code still works fine  as a macro. It's when I
attach it to a button  I get the problem.

Obviously I can work round by assigning a key to the macro, and I don't want
to take up more of either of your time. Equally I'm bugged by not being able
to resolve it - it seems so simple even to my limited abilities - and would
be happy to hear any other ideas.

Answer #6    Answered By: Omar Walker     Answered On: Feb 10

I'm quite surprised the macro works on its own since there is a
problem with this line:

The Cells statement wants to see (row,column) values  after it rather
than "A1" or some such. Change the code to either:

Answer #7    Answered By: Bonnie Hughes     Answered On: Feb 10

It will work if you create a button  using the form toolbar, copy the code into a
module (without Private before Sub) and then assign the macro to the button. I
have no idea why it works this way but not with the ControlBox button! I have
attempted both - one works, one doesn't.

Perhaps someone else will be able to help with the logic of it!

Answer #8    Answered By: Percy Morgan     Answered On: Feb 10

The project is a register and accounts system for a
voluntary playgroup here in England. This piece of automation will mean more
of the volunteers can do the admin, so much appreciated.

Answer #9    Answered By: Aaron Kennedy     Answered On: Feb 10

I know you are happy with the answers you have already received, but
here is a very short and elegant solution that does work reliably.

Always look up the on line Help in the VB Editor, there is a lot of
good stuff there.
It pays to put your cursor in the key word, (ie Copy in this case)
and hit the F1 key - this will display its on line Help. This
example is shown in the on line Help for 'Copy' being used with
the 'Range' object.

Private Sub cmdPasteFees_Click()

ActiveSheet.Range("A52:E92").Copy _

End Sub

Answer #10    Answered By: Ana Silva     Answered On: Feb 10

Thanks especially for the general tip, which I'm sure will be helpful. When
I say I'm a newbie  this was literally the first time I'd tried to use VBA!

The code you suggested doesn't quite do what I wanted on this occasion - I
needed to use paste  special to pick up only values  - but I'm storing it away
for future use.

Didn't find what you were looking for? Find more on Newbie question - paste special Or get search suggestion and latest updates.