Copy data from data sheet

[Steve]

Hi everyone. I have a large data sheet, with the first 6 columns of
data I need. Column 1 of the data sheet is an invoice number. I then
have a calculation sheet. In cell A2 of the calculation sheet is an
invoice number. Can I have vba scan column A of the data sheet for
the invoice number in A2 and return columns 1 thru 6 into the
calculation sheet? Thanks!!!

 

[Barb Reinhardt]

You can, but why wouldn't use use VLOOKUP instead?

 

[Steve]

Because I have multiple instances of the same invoice number. I need
to copy in every instance of the given invoice number. Thanks!

 

[GTVT06]

Hi everyone.  I have a large data sheet, with the first 6 columns of
data I need.  Column 1 of the data sheet is an invoice number.  I then
have a calculation sheet.  In cell A2 of the calculation sheet is an
invoice number.  Can I have vba scan column A of the data sheet for
the invoice number in A2 and return columns 1 thru 6 into the
calculation sheet?  Thanks!!!

This will find and select your data, but I'm not sure where you want
it pasted.

Sub FindRange()
Dim cell As Range
'Assuming Invoice numbers are located in
'L1:L50 as an example
For Each cell In Worksheets("Data").Range("L1:L50")
If cell.Value = Worksheets("Calc").Range("A2").Value Then
Worksheets("Data").Activate
Range(cell.Address & ":" & cell.Offset(0, 5).Address).Select
End If
Next cell
End Sub