Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Apr 21 '13 at 21:11
    
@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. –  Dave Apr 22 '13 at 4:06

1 Answer 1

up vote 3 down vote accepted

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.

enter image description here

share|improve this answer
    
Thanks very much. Works exactly like I needed it to. =D –  Dave Apr 22 '13 at 11:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.