Sub Foo()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Dim Fnd As Range
Dim Ary As Variant
Dim i As Long
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
Ary = Array("Total", 24, "t-4", 4, "t-3", 5, "t-2", 6, "t-1", 7, "Behr SOP = t0", 8, "t1", 9, "t2", 10, "t3", 11, "t4", 12, "t5", 13, "t6", 14, "t7", 15, "t8", 16, "t9", 17, "t10", 18, "t11", 19, "t12", 20, "t13", 21, "t14", 22, "t15", 23)
'-------------------------------------------------------------
'Open file with data to be copied
Set Fld = Application.FileDialog(msoFileDialogFolderPicker)
With Fld
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show = -1 Then
spath = .SelectedItems(1)
Filename = Dir(spath & "\*NAME*.xl*")
Set wbCopyFrom = Workbooks.Open(spath & "\" & Filename)
End If
End With
'--------------------------------------------------------------
'Copy Range
For i = 0 To UBound(Ary) Step 2
Set Fnd = wsCopyFrom.Range("5:5").Find(Ary(i), , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
wsCopyFrom.Range(Fnd.Offset(1), wsCopyFrom.Cells(wsCopyFrom.Rows.Count, Fnd.Column).End(xlUp)).Copy
wsCopyTo.Cells(7, Ary(i + 1)).PasteSpecial xlPasteValues
End If
Next i
Application.CutCopyMode = False
wbCopyFrom.Close SaveChanges:=False
End Sub
After running the program I keep getting this message, "Run-time error 91: Object variable or with block variable not set"
Bookmarks