Automatically move entire rows from one worksheet into another worksheet in the same workbook - please see the code that I used and explain why i have

[brandidk]

Good morning, I have an excel workbook that has two tabs. one is 2015 and the other is Shipping. Both sheets are identical. I have added a code in column Y (date shipped) and have received a Run-time error of "1004' - we can not do that to a merged cell. Please advise what I need to change in order for this to work. Please see the code below - the BOLD line is the error line.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets("Shipped").Cells(Rows.Count, "Y").End(xlUp).Row + 1
Rows(Target.Row).Cut Destination:=Sheets("Shipped").Rows(Lastrow)
Rows(ActiveCell.Row).EntireRow.Delete
End If
End Sub

 

[JoeMo]

If the destination tab is "Shipping", then replace "Shipped" with "Shipping" and you should be fine.

 

[brandidk]

Sorry the second tab does say Shipped not Shipping.

 

[JoeMo]

Sorry the second tab does say Shipped not Shipping.

In that case, where are the merged cells?

 

[brandidk]

Column A, B, C, D, E, F, G, H, I, J, R, S, T, U, V, W, X, Y - row 2 & 3 are merged together
This is the same for each row (sets of 2 rows are merged together)
2 & 3, 4 & 5, and so on

 

[JoeMo]

Column A, B, C, D, E, F, G, H, I, J, R, S, T, U, V, W, X, Y - row 2 & 3 are merged together
This is the same for each row (sets of 2 rows are merged together)
2 & 3, 4 & 5, and so on

On which sheet(s)?

 

[brandidk]

Both sheets are identical

 

[JoeMo]

Both sheets are identical

You can't cut part of a merged area. If the cells on the source sheet need to be merged, alter the code to unmerge them, carry out the cut, and then merge them again. The destination cells must be unmerged too.

 

[brandidk]

Is there any other code that I can use or a formula that would do what we require it to do?

 

[JoeMo]

Is there any other code that I can use or a formula that would do what we require it to do?

I don't really understand exactly what you require or why the cells need to be merged. In general, merged cells are a headache - is there an option that would work for you?