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

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
The third one fails because Paste is not a method for the Range object, but it is a method for the Worksheet object.
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top