Copy & Paste cells dependent on value

[Sentos]

Hi,

Im creating a financial projection worksheet but haven't decided on when
each product will launch.
I want C6 value if = 1 to copy E55 and paste at E55
if C6 = 2 copy E55 and paste at f5:q5
if C6 = 3 copy E55 and paste at g5:r5
ect

THis is the code I came up with:
Sub Macro1()
Sheets("Revenue table").Select
cval = Range("C6")
Sheets("OpenCAM Quarterly Projections").Select
Range("E55").Select
Selection.Clear
Range("E5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
If cval = 1 Then
Range("E5").Select
ActiveSheet.Paste
ElseIf cval = 2 Then
Range("F5").Select
ActiveSheet.Paste
ElseIf cval = 3 Then
Range("G5").Select
ActiveSheet.Paste
End If
End Sub

It runs fine when value =1 but anything else it has an error msg.
Error msg: Run-time error 1004
click a single cell, and then paste
or select a rectangle that's te same size and shape, then paste

Can anyone please help?

 

[Per Jessen]

Try this sligthly modified code:

Sub Macro1()
Sheets("OpenCAM Quarterly Projections").Select
Range("E55").Clear
Range("E5", Range("E5").End(xlToRight)).Copy
Select Case Sheets("Revenue table").Range("C6")
Case 1
Range("E5").Paste
Case 2
Range("F5").Paste
Case 3
Range("G5").Paste
End Select
Application.CutCopyMode = False
End Sub

Regards,
Per

 

[Per Jessen]

Just read your post again, and realized that you want to copy a fixed
range,so this is better. Also I do not understand why you clear E55,
and then try to copy this (empty) range. In this case I think using
'Cut' rather than ' Clear & Copy' is what you need:

Sub Macro1()
Sheets("OpenCAM Quarterly Projections").Select
Range("E55").Cut
Select Case Sheets("Revenue table").Range("C6")
Case 1
Range("E5").Paste
Case 2
Range("F5").Paste
Case 3
Range("G5").Paste
End Select
Application.CutCopyMode = False
End Sub

Regards,
Per