Copying merged cells (3 cells) based on conentes of any of 3 cells to right

I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:

Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer

For i = 5 To 29 Step 3
If Sheets("ICU").Range("L" & i).Value = "X" And Sheets("ICU").Range("A" & i).Value <> "" Then
Sheets("CAPU summary").Range("A" & i).End(xlUp).Offset(1).Value = Sheets("ICU").Range("A" & i).Value
End If
Next i

For i = 5 To 29 Step 3
If Sheets("ICU").Range("L" & i).Value = "X" And Sheets("ICU").Range("B" & i).Value <> "" Then
Sheets("CAPU summary").Range("B" & i).End(xlUp).Offset(1).Value = Sheets("ICU").Range("B" & i).Value
End If
Next i

For i = 5 To 29 Step 3
If Sheets("ICU").Range("L" & i).Value = "X" And Sheets("ICU").Range("c" & i).Value <> "" Then
Sheets("CAPU summary").Range("c" & i).End(xlUp).Offset(1).Value = Sheets("ICU").Range("c" & i).Value
End If
Next i

For i = 5 To 29
If Sheets("ICU").Range("L" & i).Value = "X" Then
Sheets("CAPU summary").Range("l" & i).Value = Sheets("ICU").Range("d" & i).Value
End If
Next i
End Sub

To do three columns at once, you can do this:

Please Login or Register  to view this content.

Also, wrap your code in [CODE] tags, like above. I'm having trouble visualizing what you're talking about with merged cells. Can you provide a sample worksheet?

Here is a shot of the merged cells. I only want to copy if value = "X" in column L (one of 3 cells aligned with name. I also want to copy column E if there is an "X" in on of the 3 columns but it has to line up with the correct name. I have not tried the code yet but will this afternoon.

Capture.PNG

I tried the code and it only copied the first column, not all 3. Also, did my screen shot make it to my last post so you could get a view of my spreadsheet.

Thanks

I want to copy first 3 columns to a summary sheet but only want to copy those columns if there is an "X" in on the 3 cells in coluumn L i.e. name in Column A, cell 5 to 7 does not have any "X" in Column L row 5, 6 and 7 - however name in Column A, cell 8 to 10 has at least 1 "X" in COlumn L row 8, 9 and 10 so I want to copy that name and 2 other columns. There are multiple sheets that I will also add so that I will have the summary sheet only with names that Hav an "X" in column L. Does this help.

I have attached a sample file. I did not realize that merged cells create problems. I can modify my design to handle that but the merged cells make it much easier.

It is really close but I only want those cells in Column D copied that have an "X" in Column L. I have attached what I am looking for.

Many thanks for you help. I am new to macros and am trying to learn them but this is a difficult one for me.

It is copying all the correct information over which is great, but the information should be copied in the first empty row starting with A5 - this will be necessary because I will apply the same macro to the other sheets in this workbook. Hopefully, I will be able to accomplish that after this first one is perfect. I am trying to follow your code and realize that I have a lot to learn. Thanks for all your help

If there is a better way to do what I am attempting, than I would gladly accept that. The summary only has contain the necesaary data that have an "X" in column L.

Thanks for helping me through this and I am looking forward to your better way - currently, this is beyond my capabilitites.

Thank-you so much, it does work. One interesting thing I found is that running CopyICUCAPUv3, it will not recognize a lower case x. However, CopyICUCAPU does recognize the lower case x. I will now work to add the other tabs to te summary.

Thanks again