How to make a button that will send all values from one table to a row in another table

I'm not even sure if this is possible, and Google is turning up nothing for me. I have a table with data in C6:F12,G6:G8. I want to insert a button that will send all of this data into a single NEW row of another table. This table will be located further down the the worksheet. Depending on the size of the graphs I insert, the first set of data will go around 50A:50AD.

Data from G7 and G8 would be combined and placed in 50A. Then C6:F6 would go in 50B:50E, C7:F7 would go in 50F:50I, C8:F8 would go in 50J:50m, C9:F9 would go in 50N:50Q, C10:F10 would go in 50R:50U, C11:F11 would go in 50V:50Y, C12:F12 would go in 50Z:50AC, and G6 would go in 50AD.

Each time the button is pressed a new row would be created, so the second set of data would be in the same columns, but in row 51, the third in row 52, etc.

1 Answer

Step by step:

1. In your workbook, hit Alt-F11 to open the Visual Basic Editor (VBE)

2. Click Insert > Module from the menu and paste the following into the big code window

   Option Explicit
   Sub copyRow()
   Dim ws As Worksheet
   Dim lRow As Long
   ' define which worksheet to work on, i.e. replace Sheet1 with the name of your sheet
   Set ws = ActiveWorkbook.Sheets("Sheet1")
   ' determine the last row with content in column A and add one
   lRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
   ' combine G7 and G8 and copy into column A, next empty row
   ws.Range("A" & lRow) = ws.[G7] & " " & ws.[G8]
   ' copy the other cells into their ranges
   ws.Range("C6:F6").Copy ws.Range("B" & lRow)
   ws.Range("C7:F7").Copy ws.Range("F" & lRow)
   ws.Range("C8:F8").Copy ws.Range("J" & lRow)
   ws.Range("C9:F9").Copy ws.Range("N" & lRow)
   ws.Range("C10:F10").Copy ws.Range("R" & lRow)
   ws.Range("C11:F11").Copy ws.Range("V" & lRow)
   ws.Range("C12:F12").Copy ws.Range("Z" & lRow)
   ws.Range("G6").Copy ws.Range("AD" & lRow)
   ws.[A1].Select
   End Sub

3. Close the VBE

4. In your sheet, insert a button or a shape. Right-click the shape and select "Assign Macro". Select the macro called "copyRow"

5. In column A, place some text in a cell to mark the beginning of your data table. Then click the button and the current values of the table and ranges in rows 6 to 12 will be copied into the next empty row.

This scenario is based on the assumption that every new row of data will have some value in G7 and G8, so every row in the table below will have a value in column A.