Button Macro to copy multiple cells to new row

March 5, 2012 at 05:42:19
Specs: Windows Vista
Hello all and thanks for taking the time to help.

I am trying to build a macro button that will when clicked:

1. Insert a new row at a specific point (say under row 20),
2. Copy values from cells B4,B5,B6,B7 etz to the new row (B4 to A20, B5 to B20, B5 to C20, etz)
3. Clear the values from cells B4,B5,B6 etz.

I have been able to find/build the first and 3rd part but cannot seem to combine it with the With .Copy code i am using for the second.

Regards, and thanks again for the help


See More: Button Macro to copy multiple cells to new row

Report •


#1
March 5, 2012 at 06:35:26
It would help if you posted what you already have so we can see if it simply needs a minor modification.

Please click on the following line before you post your code and read the instructions on how to post code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 5, 2012 at 06:47:16
Solved it (partially) :)

First part will insert the new row, (moving all filled rows one down).
Then the values are copied (used names so to make it more flexible)
Then cleared the Values from the input fields.

Sub AddToList_Button2()

Worksheets("Form").Range("A24").EntireRow.Insert

Worksheets("Form").Range("AN").Copy Worksheets("Form").Range("A24")
Worksheets("Form").Range("AM").Copy Worksheets("Form").Range("B24")
Worksheets("Form").Range("BP").Copy Worksheets("Form").Range("C24")
Worksheets("Form").Range("BCR").Copy Worksheets("Form").Range("D24")

Worksheets("Form").Range("AN", "AM", "BP", "BCR").ClearContents

End Sub

I am still looking how to only copy the value of the cells, and not the format and formulas.
And how to check if the fields are empty (should give a pop up error)


Report •

#3
March 5, 2012 at 07:04:33
Try this:

Worksheets("Form").Range("AN").Copy 
   Worksheets("Form").Range("A24").PasteSpecial Paste:=xlPasteValues

re: And how to check if the fields are empty

What fields?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question