Forum Discussion
Copy the row until it reaches "Waiting for data"
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 SubYou can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.
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.
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 SubYou can try these lines of code. In the attached file you can click the button in cell Q2 to run the macro.
- System is rebooting so I will have to test this out next week. Thanks I will give you the heads up.