locked
Copying specific data from one sheet to another. RRS feed

  • Question

  • Basically I have two sheets, one contains a large amount of raw data with titles running along the first row. Each of these titles relates to a cell on the second sheet. The second sheet is a template in which you can substitute data in order to produce a quote for an individual. On the first sheet, each row (not including the first) represents all the information for a single individual. I need a code in VBA which will first of all, copy the necessary data from sheet 1 into the correct places in sheet 2 and secondly loop this process moving down row by row. Thanks in advance.    
    Thursday, July 7, 2016 1:32 PM

All replies

  • I have assumed your first sheet is named Sheet1, and the template is named Sheet2.  And, once the values are there - what else you need to do - print?

    Sub TestMacro()
        Dim r As Long
        Dim rl As Long

        rl = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

        For r = 2 To rl
            'Write the data from each row into specific cells of sheet2
            Worksheets("Sheet2").Range("C2").Value = Worksheets("Sheet1").Cells(r, "A").Value
            Worksheets("Sheet2").Range("H4").Value = Worksheets("Sheet1").Cells(r, "B").Value
            Worksheets("Sheet2").Range("K3").Value = Worksheets("Sheet1").Cells(r, "C").Value

            'Repeat the above line as many times as needed,
            'changing the specific cell address for Sheet2 (on the left)
            'and incrementing the column from Sheet1 (on the right)
            'I hope you see the pattern

            'And, once the values are there - what else you need to do - print?
        Next r
    End Sub
    Thursday, July 7, 2016 7:46 PM