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?

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 <Sen...@discussions.microsoft.com> 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?

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 <perjesse...@hotmail.com> 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 <Sen...@discussions.microsoft.com> 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 -