Sheets(MyArr(i)).Range("L2:L20") to a variable range of column L
I have a couple attempts commented out trying to make a variable range for column L for each sheet in the array.
The hardcoded range works okay, however.
Thanks.
Howard
Sub Greater_Than_Copy()
Dim MyArr As Variant
Dim c As Range
Dim i As Long
Dim rngA As Range, lrMA As Range
Dim lrCl As Long
lrCl = Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row
With Sheets("Sheet5").Range("A1:L" & lrCl)
.ClearContents
End With
MyArr = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
Application.ScreenUpdating = False
For i = LBound(MyArr) To UBound(MyArr)
With Sheets(MyArr(i))
'Set rngA = Range("L2", Range("L2").End(xlDown))
'Set rngA = Sheets(MyArr(i)).Range("L2", Range("L2").End(xlDown))
Set rngA = Sheets(MyArr(i)).Range("L2:L20")
For Each c In rngA
If c.Value 0 Then
c.Offset(, -11).Resize(1, 12).Copy Sheets("Sheet5").Range("A" & Rows.Count) _
.End(xlUp)(2)
End If
Next 'c
End With
Next 'i
Application.ScreenUpdating = True
End Sub
|