Place this macro in a regular module in your "Generator.xls" workbook. Assign it to the "Create" button you have.
Sub CopyCell()
Application.ScreenUpdating = False
Dim flder As FileDialog
Dim FileName As String
Dim FileChosen As Integer
Dim wkbSource As Workbook
Dim wkbDest As Workbook
Set wkbDest = ThisWorkbook
Dim rng As Range
Dim LastRow As Long
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.InitialFileName = "c:\"
flder.Filters.Clear
flder.Filters.Add "Excel Macros Files", "*.xls"
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set wkbSource = Workbooks.Open(FileName)
With wkbSource
LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Each rng In .Sheets("Sheet1").Range("A2:A" & LastRow)
If rng = "Data" And rng.Offset(0, 1) = "Open" Then
wkbDest.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = rng.Offset(0, 2)
End If
Next rng
.Close savechanges:=False
End With
Application.ScreenUpdating = True
End Sub
Bookmarks