How aboutVBA Code:Sub ste33uka() Dim i As Long For i = 6 To 10 Sheets(CStr(i)).Range("A70:F130").Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1) Next i End Sub
Glad to help & thanks for the feedback.
Sub ste33uka()
Dim i As Long
For i = 6 To 10
With Sheets(CStr(i))
If LCase(.Range("Z1").Value) = "yes" Then
.Range("A70:F130").Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
.Range("A70:F130").Copy Sheets("NoData").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End With
Next i
End Sub
How aboutVBA Code:Sub ste33uka() Dim i As Long For i = 6 To 10 With Sheets(CStr(i)) If LCase(.Range("Z1").Value) = "yes" Then .Range("A70:F130").Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1) Else .Range("A70:F130").Copy Sheets("NoData").Range("A" & Rows.Count).End(xlUp).Offset(1) End If End With Next i End Sub
Just add an elseif