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.

Where is the other table? Can you please specify where each cell value should go? You want to copy from two different ranges that each have multiple rows. You want to create data in one single row. Where is that row and where does B2 go? Where does B3 go? Where does B4 go? etc. You need to create a logical mapping between source and target, then it can be put into VBA.

@teylyn - I've updated my original post with more specific information. Also, some of my cell locations changed, but they are in their final position now.

1 Answer 1

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 

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.