Displaying row information from one sheet onto another sheet and being able to loop through the rows

[Simmo128]

Hi,

Sorry in advance I know there is a lot of information regarding this on other posts but I cant seem to find my scenario specifically and my VB skill is limited and am having issues adapting what I have found so any help would be appreciated.

So the scenario here is I have a summary worksheet which is effectively a test invoice, on another tab I have entries for test information to be copied over to it, each row represents different information regarding a separate invoice. I have created some very basic VB code so when a the macro tied to a button is run the new rows data is copied to the invoice

Private Sub CopyInvoiceData()
'Copy Order Number
Worksheets("Test Invoice").Range("F22").Value = Worksheets("TAS_AllReceiptMatch").Range("A2").Value
'Copy Vendor Number
Worksheets("Test Invoice").Range("L22").Value = Worksheets("TAS_AllReceiptMatch").Range("B2").Value
'Copy Document Number
Worksheets("Test Invoice").Range("J22").Value = Worksheets("TAS_AllReceiptMatch").Range("C2").Value

End Sub

The problem here is the macro is very static and if I wanted to copy over the information regarding row 3 I would need to go into it and edit ranges A2,B2 & C2

What I really need is something that will loop through the results and copy the next row of results to the static location on the summary when the macro is executed, so perhaps creating a list, assigning a variable then applying it to the ranges?

Thanks in advance

 

[BSALV]

Private Sub CopyInvoiceData()
     Dim TI, TAS

     Set TI = Worksheets("Test Invoice")                        'abbrev. for that sheet
     Set TAS = Worksheets("TAS_AllReceiptMatch")
     For i = 0 To 10                                            'max number (+1) of rows possible
          If Len(TAS.Range("A2").Offset(i).Value) = 0 Then Exit For     'if not Order number = exit loop
          TI.Range("F22").Offset(i).Value = TAS.Range("A2").Offset(i).Value     'Copy Order Number
          TI.Range("L22").Offset(i).Value = TAS.Range("B2").Offset(i).Value     'Copy Vendor Number
          TI.Range("J22").Offset(i).Value = TAS.Range("C2").Offset(i).Value     'Copy Document Number
     Next
End Sub

 

[Jamie McMillan]

Hello,

I am guessing that you only want one row of data each time. Row 4, or row 7 to be copied.

Here you put the row number you wish in cell P1, or any other cell and run the macro. NB It acctually replaces each row and stops looping on the row in P1.

Sub Invoice()
Dim i As Integer
i = 1
Application.ScreenUpdating = False
For i = 1 To Range("P1").Value
Worksheets("Test Invoice").Range("F22").Value = Worksheets("TAS_AllReceiptMatch").Cells(i, 1).Value
Worksheets("Test Invoice").Range("L22").Value = Worksheets("TAS_AllReceiptMatch").Cells(i, 2).Value
Worksheets("Test Invoice").Range("J22").Value = Worksheets("TAS_AllReceiptMatch").Cells(i, 3).Value
Next i
Application.ScreenUpdating = True
End Sub

Jamie

 

[Simmo128]

Thanks so much guys