SOLVED

Copy the row until it reaches "Waiting for data"

Copper Contributor

I have two columns both using a Index Match formula: 

=IF(N3>0,INDEX($F$1:$I$1570,MATCH(N3,$F$1:$F$1570,0),2),"Waiting for data...")

 

The information in the 1st column shown are codes scanned into the cell.

The information populated into the 2nd and 3rd columns are formulas (index and matched) found using column 1.

Is there a macro way to select and copy down the row until it reaches "Waiting for data" then pasting those values into another sheet?   

753759143350010-01534-11Montana 680T Handheld Gps
753759209384010-01771-00Approach Z80 - Golf Laser Range Finder With Gps - (010-01771-00)
 Waiting for data...Waiting for data...
 Waiting for data...Waiting for data...
4 Replies

@Keith_Massey 

=FILTER(N3:O7,O3:O7<>"Waiting for data...")

An alternative could be FILTER if i correctly understand what you want to do.

waiting for data.JPG

I have two columns both using a Index Match formula:
=IF(N3>0,INDEX($F$1:$I$1570,MATCH(N3,$F$1:$F$1570,0),2),"Waiting for data...")

The information in the 1st column IS N3 shown are codes scanned into the cell.
The information populated into the 2nd and 3rd columns are formulas (index and matched) found using column 1 N3. So once a value is scanned into column 1/N3 the index and match dynamically populate columns 2 and 3.
I need to find a way to copy the values - NOT THE FORMULAS - from column 2 until it finds a row that has no value to match in the index: "Waiting for data" then paste all those rows/values into another sheet.

best response confirmed by Keith_Massey (Copper Contributor)
Solution

@Keith_Massey 

Sub copy()

Dim i, j As Long

i = Application.WorksheetFunction.Match("Waiting for data...", Range("O:O"), 0)

Range(Cells(3, 15), Cells(i - 1, 16)).copy
Sheets("Tabelle2").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

You can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.

copy the rows.JPG

System is rebooting so I will have to test this out next week. Thanks I will give you the heads up.
1 best response

Accepted Solutions
best response confirmed by Keith_Massey (Copper Contributor)
Solution

@Keith_Massey 

Sub copy()

Dim i, j As Long

i = Application.WorksheetFunction.Match("Waiting for data...", Range("O:O"), 0)

Range(Cells(3, 15), Cells(i - 1, 16)).copy
Sheets("Tabelle2").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

You can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.

copy the rows.JPG

View solution in original post