Macro to copy row from one area of sheet to another

[missfionaelizabeth]

Hello! Long time lurker, and I've always been able to find my answers here. But try as I might, I just can't solve this one!!!

Basically, I have a sheet in which I need to enter learner details for learners booking onto a course. The sheet doubles as the signing in sheet (so the list can just be printed with no additional faffing necessary) so the data does not begin until row 16. Columns B:J contain the information I'll require.

So, sometimes people cancel, right? And sometimes, Business Admin can get a little ... sloppy. I don't want people to delete someone off the course without copying and pasting the booking into a section further down the spreadsheet (data starting on row 71, column B) and entering the cancellation date in case there is any dispute.

I have a button called 'Cancel Booking', and what I want to do is click on the appropriate row with the details of the person who is cancelling entered, click 'Cancel Booking' button, and then the macro to run and copy and paste the details in the Cancellations section starting in B71.

Paste from:

To:

(this is a very quick recreation of the sheet I did on my home OpenOffice - if there's anything else I can do to help make it more clear??)


So far I've got this to copy and paste the details further down, but if there is more than one cancellation rather than looking for the next empty row it pastes directly over the row 71 entry. Futher to this, it keeps beginning the paste in column A rather than B. I'm also having problems with my merged cells - sometimes it will say that 'this will only work with merged cells of identical sizes', but they're in the same column and all the same size?

Sub BookingCancellation()

 

Selection.Copy

 

Dim lastrow As Long

lastrow = Range("b71").End(xlUp).row

 

Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 
End Sub

Is there anybody on this planet who can help me move this data further down my sheet, then have it look for the next empty row when the next cancellation comes along rather than overwriting?

--- also, is there anyway I can add Input Box so that a date could be entered into column H after the data is pasted?

I'm struggling quite a bit, and it doesn't help that I have Excel at work and OpenOffice at home so can't work on it outside of core hours.

I would be so grateful if anyone can give me any insight!!!!

 

[dmartin35]

missfionaelizabeth,

I would suggest removing the merged columns which would mean your data would be in columns A:F. The below code will move the data (starting in column B). With this code you don't have to manually select the data you want to move, only be on the row you want to move.

Sub MoveData()
Dim r As Long
Dim lr As Long


r = ActiveCell.Row
lr = Range("B" & Rows.Count).End(xlUp).Row


Range(Cells(r, "B"), Cells(r, "F")).Copy Destination:=Range(Cells(lr+1, "B"), Cells(lr+1, "F"))
Range(Cells(r, "B"), Cells(r, "F")).ClearContents


End Sub

See if that gets you going in the right direction.

David

 

[missfionaelizabeth]

This worked absolutely perfectly!! Thank you so much.

Quick question - and you don't have to answer because I can live without it - but is there anyway to get an InputBox to put data into a specfic cell? So the data is pasted into the available row, and then the input box will ask for a date to be inserted in the Date Cancelled column? Or is there a way for the date to automatically be filled in rather than complicating it with a manual input?

Thanks again, if you can help or not!

 

[dmartin35]

Try this.

Sub MoveData()
Dim r As Long
Dim lr As Long
Dim tDate As String


r = ActiveCell.Row
lr = Range("B" & Rows.Count).End(xlUp).Row


Range(Cells(r, "B"), Cells(r, "F")).Copy Destination:=Range(Cells(lr + 1, "B"), Cells(lr + 1, "F"))
Range(Cells(r, "B"), Cells(r, "F")).ClearContents


tDate = InputBox("Enter the cancellation date.")
Cells(lr + 1, "G") = tDate


End Sub

 

[missfionaelizabeth]

This worked absolutely perfectly, you're an absolute star - thank you so much for your help!!!

 

[dmartin35]

You're welcome.