Sub grab_patents()
n = 2
var_workbook = _
  "C:\Users\Christian\Documents\Uni\ESADE\Thesis\data\Pharma\Test\patents.xlsm"
var_sheet_patents = "patents"
var_sheet_db = "Sheet1"
var_numasg = "numasg"
 
For i = 1 To 11
 
var_workbook_db = "C:\Users\Christian\Documents\Uni\ESADE\Thesis\data\Raw" & _
  "data\Patent data\Neuer Ordner\" & i & ".xlsx"
Workbooks.Open (var_workbook_db)
 
 
For Each numasg In Workbooks(var_workbook).Worksheets(var_numasg).Range( _
  "A2:A92")
 
var_search = numasg
 
For Each var_cell In Workbooks(var_workbook_db).Worksheets(var_sheet_db).Range( _
  "F2:F50001")
 
If var_cell = var_search Then
 
var_zeile = var_cell.Row
 
Workbooks(var_workbook_db).Worksheets(var_sheet_db).Range(Cells(var_zeile, 1), _
  Cells(var_zeile, 35)).Copy Destination:=Workbooks(var_workbook).Worksheets( _
  var_sheet_patents).Cells(n, 1)
n = n + 1
 
End If
Next
Next
 
Workbooks(var_workbook_db).Close SaveChanges:=False
Next
 
End Sub