VBA solution to move rows between sheets without overwriting data

[Orisno]

Greetings everyone,

I am attempting to use VBA to create the following workbook. I am very new to VBA so please bear with me.

There are two sheets in the workbook: both sheets contain identical table setups (they have the same number of columns and identical headers), but the information within the tables may be different. See the example below.

Sheet 1 - Awaiting Publication

Book Title​	Cost​	Author​
How to Build Your Own Boat	$10.50	Mike Skipp
Ramen for Beginners	$8	Susan Smith
The Secret Life of Mice	$11	Stephanie Johnson

Sheet 2 - Published Titles

Book Title​	Cost​	Author​
Make Money by Writing	$100	Steven Rich
Origami 101	$5	T. S. Privett
Cars Through the Ages	$15	Angela Ames

I would like it so that if a book were published, a user could double-click the title in Sheet 1, and the entire row would be moved and appended to the bottom of Sheet 2. I am using this code on the Awaiting Publications sheet that I've found online.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


If Target.Column = 1 Then
    On Error Resume Next
    Target.EntireRow.Copy Sheets("Published Titles").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


    If Err.Number <> 0 Then
        Target.EntireRow.Copy Sheets("Published Titles").Range("A1")
    End If
    On Error GoTo 0

End If

End Sub

So far, this works once. Double clicking a title in Sheet 1 copies the row over to the next available row in Sheet 2. However, if I attempt to do this multiple times, the bottom row of Sheet 2 is always overwritten. So, if I double-click How to Build Your Own Boat, then double-click Ramen for Beginners, the table ends up looking like this:

Sheet 2 - Published Titles

Book Title​	Cost​	Author​
Make Money by Writing	$100	Steven Rich
Origami 101	$5	T. S. Privett
Cars Through the Ages	$15	Angela Ames
Ramen for Beginners	$8	Susan Smith

However, I want it to look like this:

Sheet 2 - Published Titles

Book Title​	Cost​	Author​
Make Money by Writing	$100	Steven Rich
Origami 101	$5	T. S. Privett
Cars Through the Ages	$15	Angela Ames
How to Build Your Own Boat	$10.50	Mike Skipp
Ramen for Beginners	$8	Susan Smith

Ideally, the script would also delete the row in Sheet 1 that was being copied over, but that's less important. What am I missing in the code that's causing this error?

Thanks in advance!

 

[anisalewis]

Thank you for all your help Mark, and my apologies for the late reply. Life has been hectic this past month.

For posterity's sake, I figured out my issue. On the real spreadsheet I was working on (not this example spreadsheet), the column that I was using for my double-click contained no values. I later learned that these functions will not work correctly if you attempt to use them on empty cells. I input some placeholder values and now the script works perfectly.

Great insight! I didn't realize empty cells could cause issues with these functions. Thanks for sharing your solution!