Need help using one sheet to determine which row to copy data from

[drmingle]

I have a single workbook with 2 worksheets:

• Azn_TrainA {Has row data A-J which I need to copy and paste at the bottom of Azn_TrainA}
• Extract {Column E has numeric data which states which row I need to copy from Azn_TrainA}

I would like this to be a loop until all data from Extract has been cycled through. Also, I need an adjustment of 1 because I have a header in Azn_TrainA. Lastly, I need to be sure and paste three times the value at the end of the data for each row copied.

For example,

1. Extract states to pull row 3
2. Row 3 is really on row 4 (due to header row in Azn_TrainA).

Below is some code that I am starting with...(may not be useful).

Option Explicit

Sub PopulationBuilder()
'
' Objective: to loop through data located within 'Extract' tab and locate, copy, and paste
'                   data at the bottom of Azn_TrainA 3 times in a row.
'

'Pull value in column E to the end of populated column; value plus 1 equals row to copy for Azn_TrainA
    Sheets("Extract").Select
    Range("E6").Select

'Sheet with data present from A through J; entire row should be copied and pasted 3 times in a row at the
'end of all the data on sheet.
    Sheets("Azn_TrainA").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Selection.End(xlDown).Select
    Range("A64414").Select
    ActiveSheet.Paste
    Sheets("Extract").Select

End Sub

I appreciate the help in advance. I am using Excel 2010.

 

[JoeMo]

Try this (untested) on a copy of your sheet:

Option Explicit

Sub PopulationBuilder()
Dim nRw As Long
'Pull value in column E to the end of populated column; value plus 1 equals row to copy for Azn_TrainA
    nRw = Sheets("Extract").Range("E6").Value + 1

'Sheet with data present from A through J; entire row should be copied and pasted 3 times in a row at the
'end of all the data on sheet.
    Sheets("Amazon_TrainA").Select
    Range(Cells(nRw, "A"), Cells(nRw, "A").End(xlToRight)).Copy Destination:=Range("A64414").Resize(3, 1)
    Sheets("Extract").Select
End Sub

 

[drmingle]

Try this (untested) on a copy of your sheet:

Option Explicit

Sub PopulationBuilder()
Dim nRw As Long
'Pull value in column E to the end of populated column; value plus 1 equals row to copy for Azn_TrainA
    nRw = Sheets("Extract").Range("E6").Value + 1

'Sheet with data present from A through J; entire row should be copied and pasted 3 times in a row at the
'end of all the data on sheet.
    Sheets("Amazon_TrainA").Select
    Range(Cells(nRw, "A"), Cells(nRw, "A").End(xlToRight)).Copy Destination:=Range("A64414").Resize(3, 1)
    Sheets("Extract").Select
End Sub

Thank you for your response...

• Extract has a range in column "E"...starting at "E2"
• Since we will be copying rows from the Amazon page and bringing them to the bottom and pasting them...the Destination range will be growing (bigger than A64414 each time it loops).
• Any ideas on how to get it to loop/cycle through the Extract range in E and apply the copy/paste procedure to the Amazon Tab?

 

[JoeMo]

Not sure I understand what you want to do. Here's my version, maybe you can set me straight.
Extract sheet has a series of integers starting in E2 that define a row number (-1) in amazon sheet that you want to copy and then paste 3 times beginning in A64414 of the amazon sheet (so rows starting with A6414, A6415, A6416 have the same data records).
After that paste, go back to Extract and read the next row number from E3, go to amazon sheet copy/paste 3 times that row number (+1) in the first empty row below A64414.
Continue this process until there are no more entries in Extract column E.