Copy & Paste cells dependent on value
 

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 E5:P5 and paste at E5:P5
if C6 = 2 copy E5:P5 and paste at f5:q5
if C6 = 3 copy E5:P5 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("E5:P5").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?

Copy & Paste cells dependent on value
 

Try this sligthly modified code:

Sub Macro1()
Sheets("OpenCAM Quarterly Projections").Select
Range("E5:P5").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

On 8 Mar., 03:27, Sentos wrote:
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 E5:P5 and paste at E5:P5
if C6 = 2 copy *E5:P5 and paste at f5:q5
if C6 = 3 copy *E5:P5 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("E5:P5").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?

Copy & Paste cells dependent on value
 

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 E5:P5,
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("E5:P5").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

On 8 Mar., 03:56, Per Jessen wrote:
Try this sligthly modified code:

Sub Macro1()
Sheets("OpenCAM Quarterly Projections").Select
Range("E5:P5").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

On 8 Mar., 03:27, Sentos wrote:



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 E5:P5 and paste at E5:P5
if C6 = 2 copy *E5:P5 and paste at f5:q5
if C6 = 3 copy *E5:P5 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("E5:P5").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?- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -