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

New Member
Joined
Jan 31, 2016
Messages
19
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If the destination tab is "Shipping", then replace "Shipped" with "Shipping" and you should be fine.
 

brandidk

New Member
Joined
Jan 31, 2016
Messages
19

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 31, 2016
Messages
19

ADVERTISEMENT

Both sheets are identical
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 31, 2016
Messages
19
Is there any other code that I can use or a formula that would do what we require it to do?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top