Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I copy big ranges of cells without drag or copy/paste? | Excel Discussion (Misc queries) | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
Copy Cells/Paste Cells Programatically | Excel Programming | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
how can we copy cells comments text and paste to cells | Excel Discussion (Misc queries) |