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!

 

[MARK858]

The 2nd copy line will always overwrite starting in A1, what error are you getting to need the On Error Resume Next?

 

[Orisno]

The 2nd copy line will always overwrite starting in A1, what error are you getting to need the On Error Resume Next?

Thank you for your reply. Thus far I've not had any errors, so it could be cut. I was previously using this code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Cancel = True
With Sheets("Projects")
Target.EntireRow.Copy .Range("A" & .Cells(Rows.Count, 1).End(xlUp).Row + 1)
End With
End Sub

Which didn't have the A1 range on the copy line, but still produced the same issues with data being overwritten, so I switched to the other code.

 

[MARK858]

Thus far I've not had any errors, s

You won't the On Error Resume Next is masking any Errors

but still produced the same issues with data being overwritten

In that last code

With Sheets("Projects")

should be

With Sheets("Published Titles")

 

[Orisno]

You won't the On Error Resume Next is masking any Errors


In that last code

With Sheets("Projects")

should be

With Sheets("Published Titles")

Ah gotcha, makes sense on the error front.

For that code; I'm sorry, when I was using it previously the sheet I was using was called "Projects" and not yet "Published Titles", hence that typo. The code actually looks like this for my project now.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
Cancel = True
With Sheets("Published Titles")
Target.EntireRow.Copy .Range("A" & .Cells(Rows.Count, 1).End(xlUp).Row + 1)
End With
End Sub

I'm not getting errors of it, it's just that it seems like the "next empty line" is being calculated just from what the sheet originally had, and not by integrating any of the additional rows that were added with the use of the code. Therefore, it always overwrites the bottom row instead of growing in length as I double click more and more entries.

 

[MARK858]

Is there always data in the last row of column A in Sheets("Published Titles") at the end of each "Paste"

 

[Orisno]

Is there always data in the last row of column A in Sheets("Published Titles") at the end of each "Paste"

Yes, the last row of column A in Sheets("Published Titles") does always have data after each "paste". It's just the most recently "pasted" title, overwriting the previously "pasted" title. There is never more than one row of "pasted" data.

 

[MARK858]

A bit strange as I have tested with data here using the code in post 5 and it is pasting in the next row for me (i.e. it has grown from zero rows, by 1 row on each paste to now 4 rows after 4 pastes.

Can you post samples of your data (preferably using the forums XL2BB addin) from both the sheet with the code in it and Sheets("Published Titles")

 

[MARK858]

Just another question, are you sure that you aren't copying formulas that are recalculating to give you the same results?

Still see my previous post.