# Help with a conditional copy paste code

#### Lowell In the south

##### Board Regular
THanks for LOOKING!!!!!!!!!!
Here is my situation....

Column AS is 6 to 83 rows
Column AQ is 6 to 83 rows

AS = Yes or No in any cell 6 through 83
AQ = 1-8 in any cell 6 through 83

If AS6 = yes and AQ6 =1 Then nothing
If AS6 = yes and AQ6 =2 Then Copy (E6:F6) then paste to (AU6:AV6)
If AS6 = yes and AQ6 =3 Then Copy (E6:F6) then paste to (AU6:AV6) & (Aw6:AX6)
If AS6 = yes and AQ6 =4 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6
If AS6 = yes and AQ6 =5 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6
If AS6 = yes and AQ6 =6 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6
If AS6 = yes and AQ6 =7 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6 & BE6:BF6

I have had a tough time coming up with code to perform this. Also,I would need code like this for each cell 6 through 83 is there any other way (Shorter)?

THANKS AGAIN!!!!

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Barrie Davidson

##### MrExcel MVP
On 2002-10-15 16:57, Lowell In the south wrote:
THanks for LOOKING!!!!!!!!!!
Here is my situation....

Column AS is 6 to 83 rows
Column AQ is 6 to 83 rows

AS = Yes or No in any cell 6 through 83
AQ = 1-8 in any cell 6 through 83

If AS6 = yes and AQ6 =1 Then nothing
If AS6 = yes and AQ6 =2 Then Copy (E6:F6) then paste to (AU6:AV6)
If AS6 = yes and AQ6 =3 Then Copy (E6:F6) then paste to (AU6:AV6) & (Aw6:AX6)
If AS6 = yes and AQ6 =4 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6
If AS6 = yes and AQ6 =5 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6
If AS6 = yes and AQ6 =6 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6
If AS6 = yes and AQ6 =7 Then Copy (E6:F6) then paste to (AU6:AV6) & AW6:AX6 & AY6:AZ6 & BA6:BB6 & Bc6:BD6 & BE6:BF6

I have had a tough time coming up with code to perform this. Also,I would need code like this for each cell 6 through 83 is there any other way (Shorter)?

THANKS AGAIN!!!!

I think this code will work for you (test to make sure):

<PRE>
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).FormulaR1C1 = _
"=INDIRECT(""R""&ROW()&""C""&6-(EVEN(COLUMN())-COLUMN()),0)" & _
"*(RC43>TRUNC((COLUMN()-COLUMN(RC47))/2)+1)"
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).Copy
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).PasteSpecial _
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("AU6:BF" & Range("AQ65536").End(xlUp).Row).Replace _
What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

</PRE>

Regards,

Replies
29
Views
3K
Replies
3
Views
407
Replies
8
Views
299
Replies
3
Views
791
Replies
5
Views
2K

1,181,064
Messages
5,927,910
Members
436,576
Latest member
rovman1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back