Use a button to copy, paste & delete data

[Morgo]

Hi,

I am only new to VBA so please bear with me,

I have a worksheet that i want to use a lot of buttons or check boxes (whatever is easier to use) to copy data from a dynamic range of cells (corresponding row to the button/checkbox) & place it in another area on the same worksheet but in the next available space,

for example when i click button 2 (in cell L4) i want to copy all the data in the range A4:J4 & paste it starting from the next available row in column M! I then want that same data that is still in A4:J4 to be deleted from this range & have all the data below it move up rows to the next available row.

Am i best to use a button or checkboxs? how do i write this into the code? I have been able to write code to copy, paste & then delete but i can't figure out how to write the code so that all the buttons/checkboxs can use the same code & to get the data to automatically shift up to the next free row?

Any help would be most appreciated

Thanks
Morgo

 

[Luke M]

You would be better to use buttons, as you can't "undo" as a checkbox would seem to indicate. It sounds like you have the basics, and need help with the "new blank row" part? In which case, perhaps something like this

Sub ExampleCode()
Dim myRange As Range
Dim nextRow As Long

'what we want to copy
Set myRange = Range("A2:D10")

'Find new blank row in col M
newrow = Cells(Rows.Count, "M").End(xlUp).Row + 1

'Copy the range
myRange.Copy Destination:=Cells(newrow, "M")

End Sub

 

[Morgo]

Thanks Luke,

My original code was derived from recording what i wanted to do, so getting the copy, paste & delete worked, but it means i would have to write code for every button i put in the worksheet. Do you know of a way to grab the row from say Column B to Column J next to the corresponding button (that row only) with out being specific to a row number? i am hoping i can use the same code for all of these buttons.

I am not sure how to tell excel that when i click that button i want to copy every cell to the left of that button. I would be thinking i could use something similar to this

obviously this code is identifying the next free row, but the end part maybe change it to xlLeft, but i think that would try to justify everthing on the left of it's cell!

I can't upload the spreadsheet but i can show a picture to help explain

I want to press the button in cell L6 & grab everything from B6 to J6 & copy & paste it to the next available row in column M (currently M4) Then delete the contents of B6 to J6, Then i want everything below B6 to identify that there is a free row & move up to fill the gap! Does this help to explain it better? i am starting to confuse myself now.

 

[Luke M]

It can be done! Check out the example. I put two shape buttons in, both call the same macro, but produce different results.