What should be obvious regarding Paste w/ VBA.... Advice plz

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,961
This should be a simple 1,2,3 operation, but one I am struggling to find the answers for.
It may be trivial I realize..... I searched the help file, the board and nothing really explains the parameters.

I wanted to copy a range of data and paste it in another range. But I was unable, so I used the MacroRecorder to see what I might be doing wrong.
This was not immediately apparent to me, but after much work I located a starting point.

1. To paste from one cell to another requires PasteSpecial, not just Paste.
This was not apparent to me. It was not until I accidentally hit the autofill and in VBA and it filled it in, (I did not catch this until later that it was PasteSpecial rather than Paste). And Paste is not an option here.

2. The MacroRecorder gets away w/ it by using just Paste if it uses ActiveSheet.

3. if I fully qualify where I want the data to paste, not using the ActiveSheet object, again it fails, why?

What are the rules governing for writing single line or double line code, ie, copy/paste if you specify destination can be done in one line, if the destination is omitted then this becomes a double line piece of code but PasteSpecial is required and if the use of Activesheet eliminates PasteSpecial and Paste can be used.
Very confusing.

Is there a published set of rules regarding things of this nature?

I would appreciate any help or ideas here to find answers to the above questions, as they are important to fully grasping the programming rules here.

Here are four versions, the third fails if anyone is able to comment.
Code:
Sub Macro3()
    Range("A3:G15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O3").Select
    ActiveSheet.Paste
End Sub
Code:
Sub whatthehe()
ActiveSheet.UsedRange.Copy
Range("O18").PasteSpecial

End Sub

This one Fails
Code:
Sub whattheheck()
ActiveSheet.Range("A3").Copy
Range("O1").Paste
End Sub

Code:
Sub whattheheck2()
ActiveSheet.Range("A3:D3").Copy Destination:=Range("w3")
End Sub
Thanks,

Doug
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jc113883

Board Regular
Joined
May 29, 2006
Messages
165
Does this help:

Paste Method (Worksheet Object) Example

This example copies data from cells C1:C5 on Sheet1 to cells D1:D5 on Sheet1.

Worksheets("Sheet1").Range("C1:C5").Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")


Paste requires a worksheet object, so you don't need to use activesheet. You can use sheets(2).paste etc.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The third one fails because Paste is not a method for the Range object, but it is a method for the Worksheet object.
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
can't you just use

Code:
Range("A3:G15").Copy Destination:=Range("O18")
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,961

ADVERTISEMENT

Thanks JC and Hotpepper.
JC- clarify please- Your comment says "Paste" requires a worksheet object, so you don't need Activesheet. This sounds crossed up-
Paste requires a worksheet object, so you don't need to use activesheet. You can use sheets(2).paste etc.
The line sheets(2).paste is the worksheet method, yes?
Did you mean to write that PasteSpecial is Range method and I don't have to use a worksheet object?

From what you and Hotpepper have shown me Paste is a method for worksheet and PasteSpecial is the method for Range or Worksheet.

thanks,
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Doug

Your first point isn't quite right.

If you want to copy and insert cells you need something like this.
Code:
Range("B1:B2").Copy
Range("A4").Insert Shift:=xlDown
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,961
Hi Norie-
Thanks for adding one more option- w/ parameters.
But what do you mean my first point is not quite right? This one>>>
1. To paste from one cell to another requires PasteSpecial, not just Paste.
This was not apparent to me. It was not until I accidentally hit the autofill and in VBA and it filled it in, (I did not catch this until later that it was PasteSpecial rather than Paste). And Paste is not an option here.
If that is what you are referring to, what exactly is not correct please.

Best,

Doug
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top