Pulling specific columns from one spreadsheet

Occasional Visitor

ey everyone,

Hope everyone is doing great. I'm just stumped with this vba trying to it to work. What I'm trying to do is pull specific columns from one spreadsheet to the next.

I keep getting subscript out of range and I'm not sure why. 

Sub Test()
Dim Data, Fnd As Range, i As Long, nr As Long
Application.ScreenUpdating = False
With Sheets("Sheet2").Cells(1).CurrentRegion 
Data = .Value
For i = 2 To UBound(Data, 1)
If Data(i, 6) = "Active" Or Data(i, 6) = "LTD" Or Data(i, 6) = "LV BEN ELIGIBLE" Then
If Data(i, 52) <> OMA And Data(i, 52) <> "Teamsters" Then
With Sheets("Sheet1")
nr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range("A" & nr).Resize(, 10) = Array(Data(i, 1), Data(i, 3), Data(i, 15), Data(i, 188), Data(i, 26), Data(i, 43), Data(i, 50), Data(i, 50), Data(i, 9), Data(i, 25))
End With
End If
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

1 Reply


If the error message is "subscript out of range" on the bold line, it means that your workbook does not contain a sheet named Sheet1.