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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
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")
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
It is almost possible to help here without knowing what is actually selected before the code runs.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,913
Office Version
  1. 365
Platform
  1. Windows
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?
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top