Pasting Error in VBA

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
I have a run time error in my macro

First off, my macro is excel generated and very basic it involves selecting and I know selecting is crazy slow! Forgive me!

Selection.Copy
Range("E6").Select
ActiveSheet.Paste
Range("D7").Select
Application.CutCopyMode = False

Can anyone help with this error and if you could offer me some advice regarding how to get rid of selecting that would be icing!

Thanks in advance,

Seen!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
We would need to know what was selected to completely eliminate Select.

For what you posted you could probably use this.
Code:
Selection.Copy Range("E6")
 
Upvote 0
Not sure here, I would have to command a paste function in range D7 the problem is the error says I cannot paste a cell into another cell with a different or unrecognizable size?

Thanx
 
Upvote 0
Most likely you are copying from a cell which is part of a merged range.
Excel has an apples & oranges feeling about that.

You might try using Paste Special - Values

By the way, you can speed up your code remarkably with:

Application.ScreenUpdating = False

Place this near the start of your macro and the companion statement
Application.ScreenUpdating = True

at the end.

Here's a macro I worked out that seems to show it's possible:

Sub CopyMacro()
Application.ScreenUpdating = False
'Note: I had previously merged C3 and D3. Then I selected C3 to copy.
'Excel shows that I am selecting C3:D3.

Range("C3:D3").Select
Selection.Copy
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("C6").Select
Application.ScreenUpdating = True
End Sub

By using Paste Special Values, you allow Excel to get at the data without regard to the shape of the container.

Or something like that. In any case, you won't bring any formatting along with the data. This, incidentally, is a way to remove formulas from a range: just Copy and paste special to the same range.
 
Upvote 0
Error still occurs

I have included this argument within my Macro, but I still get an error stating Excel does not support this

Selection.Copy
Range("E6").Select
Range("D7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("D7").Select
Application.ScreenUpdating = True
Selection.Copy
Range("F6").Select
Range("E7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("E7").Select
Application.ScreenUpdating = True

Do you notice anything wrong here?

Thanks
 
Upvote 0
It is almost possible to help here without knowing what is actually selected before the code runs.
 
Upvote 0
Thanks

Sub AkamaiMacDaddy()
'
' Akamai Macro
' Macro recorded 9/22/2006 by WorldNow
'

'
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-27
Range("A7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="(08/01/2006 - 08/31/2006)", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range("D254").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-270
Range("C7").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=276
Range("D288").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-300
Range("D7").Select
ActiveSheet.Paste
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Range("C7:D250").Select
Application.CutCopyMode = False
Selection.FillDown
Selection.End(xlUp).Select
Range("D7").Select
ActiveWindow.SmallScroll Down:=309
Range("A320").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("A320"), Order1:=xlAscending, Key2:=Range( _
"D320"), Order2:=xlAscending, Key3:=Range("F320"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Here is my Macro I have bolded the argument that posses the error
Selection.Copy
Range("E6").Select
Range("D7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("D7").Select
Application.ScreenUpdating = True
Selection.Copy
Range("F6").Select
Range("E7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("E7").Select
Application.ScreenUpdating = True
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/((R254C[-4]:R16910C[-4]=RC1)*(R254C[-1]:R16910C[-1]=RC3)),R254C[8]:R16910C[8])"
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/((R254C[-4]:R116910[-4]=RC1)*(R254[-1]:R16910C[-1]=RC4)),R254C[2]:R16910C[2])"
Range("F7").Select
Selection.AutoFill Destination:=Range("E7:E250"), Type:=xlFillDefault
Range("E7:E250").Select
Range("E251").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-244]C:R[-1]C)"
Range("E252").Select
Selection.AutoFill Destination:=Range("F7:F250"), Type:=xlFillDefault
Range("F7:F250").Select
Range("F251").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-244]C:R[-1]C)"
Range("F252").Select
End Sub

Hope this is more informative!

Thanks again!
 
Upvote 0
I'll take a look at the code but because of all the selecting it's actually quite confusing as to what's going on.

What in words is the code meant to do?
 
Upvote 0
I get a report that follows the same template/format results every month and from this report I run a Lookup function: I have created a macro (well at least not functional yet) that will be consistent for each report I receive every month, thus limiting the amount of work I have to do

Its a process of steps in order to carryout the Lookup function:

Thanks again!

If you need me to post my [ws] let me know
 
Upvote 0
I see two differences from the code I suggested.

First: Application.ScreenUpdating = True appears twice.
You should first say False, then True at the end. Otherwise the screen will continue to refresh the data, at a great cost in time.

Second: This line:
Range("E7").Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Try selecting the range first, before the paste. I don't know if this
will help, but I think it will.

Range("E7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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