Copy and Paste if Condition is met (the cell contains a certain sentence)

[gingerbreadgrl]

Hi All,

I am very new to this and have been limping along with my macro through the stuff I have learned in this forum, on the web, and on youtube. I am trying to copy and paste a range of cells from one worksheet to another in a workbook if a certain cell contains a certain phrase. My code keeps returning an error. The code is below. Does anyone know what I am doing wrong? Thanks in advance!

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If Sheets("C2-LTG").Range(B2).Value = "All of my choices are the same as my partner's" Then[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheets("C1-LTG").Range("C2:DR2").Copy _
Destination:=Sheets("C2-LTG").Range("D2:DS2")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End If
[/FONT]

 

[Fluff]

Hi & welcome to MrExcel.
Try

If Sheets("C2-LTG").Range(B2).Value = "All of my choices are the same as my partner's" Then
   Sheets("C1-LTG").Range("C2:DR2").Copy Sheets("C2-LTG").Range("D2")
End If

 

[gingerbreadgrl]

Hi & welcome to MrExcel.
Try

If Sheets("C2-LTG").Range(B2).Value = "All of my choices are the same as my partner's" Then
   Sheets("C1-LTG").Range("C2:DR2").Copy Sheets("C2-LTG").Range("D2")
End If

Hi,

Thanks so much for responding. I tried your code and it gave me the error 400 message. I'm not sure what that means, bummer.

Best,
Gingerbreadgrl

 

[Fluff]

The 400 is a PITA & doesn't really mean anything of any worth.

Place the cursor anywhere in your code & step through the code using F8.
What error number & message do you get & what line gives the error?

 

[gingerbreadgrl]

Hi,

Okay so I went through the code and on the "if" line I received the Run-time error'1004': Application-defined or object-defined error. Hopefully that will be more helpful.

Thanks!

 

[Fluff]

Ok, missed that.
You're missing the "s around the range

If Sheets("C2-LTG").Range([COLOR=#ff0000]"[/COLOR]B2[COLOR=#ff0000]"[/COLOR]).Value = "All of my choices are the same as my partner's" Then
   Sheets("C1-LTG").Range("C2:DR2").Copy Sheets("C2-LTG").Range("D2")
End If

 

[gingerbreadgrl]

Oh my goodness it worked! Thank you so much, you are a Rockstar!

So I'm just curious I read a whole article on the destination function and that you need that function to paste cells to a certain destination instead of just having the cells copied to the clipboard. I was able to accomplish the copy and paste with your code and I didn't need to use the destination function. Based on your code I just needed to put the first cell of where I wanted the cells pasted (instead of the cell range with the destination function). Why is that? I'm just trying to get a better understanding of how all of this works.

Thank you so much! You made my day!

 

[Fluff]

That code does use Copy Destination, but you don't need to put in the name of the argument so

Sheets("C1-LTG").Range("C2:DR2").Copy Sheets("C2-LTG").Range("D2")

is exactly the same as

Sheets("C1-LTG").Range("C2:DR2").Copy Destination:=Sheets("C2-LTG").Range("D2")

When copying cells in this manner I find it's easier to just put in the starting point of the destination range, as it saves trying to work out the full destination range.

 

[gingerbreadgrl]

Wow that's great to know, and it saves time! Thanks again, you made my day yesterday!

 

[Fluff]

You're welcome & thanks for the feedback