PDA

View Full Version : Solved: Copy paste macro not working



xluser2007
02-23-2008, 03:46 AM
Hi All,

I am writing a main macro that involves a lot of copying and value pasting output into various cells.

In order to make the main macro read easier, I've just tried to create another macro to carry out the copy-value paste routine as follows:

Sub Copypaste(x As Range, y As Range)

x.Copy
y.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub
I then tried to test this as follows:

Sub Testcopypaste()

Call Copypaste("A12", "X12")

End Sub
Sub Testcopypaste kept running into a type mismatch error and highlighted "A12" when debugging.

Any ideas how to make this work, should Copypaste be defned as a UDF? If so, how do I go about doing this?

Any VBAGuru guidance is appreciated.

mdmackillop
02-23-2008, 03:54 AM
Not far away.
Two ways to pass the parameters, as Range or String

Option Explicit

Sub Testcopypaste()
Call Copypaste(Range("A12"), Range("X12"))
End Sub

Sub Copypaste(x As Range, y As Range)
x.Copy
y.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

'or
Sub Testcopypaste2()
Call Copypaste2("A12", "X12")
End Sub

Sub Copypaste2(x As String, y As String)
Range(x).Copy
Range(y).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

xluser2007
02-23-2008, 03:56 AM
Hi mdmackillop,

Thanks for your super-informative and super-fast response, appreciate it :friends:.

regards