dim newline as long, cel_adres as string 
'это в начале
newline =  1 
'перед процедурой
cel_adres = cells(newline, 1 ).address
.....'копирование первой строки например Range(cel_adres).past
'после копирования первой строки
newline = newline +  1 
и зацикливаешь или запускаешь процедуру несколько раз.

' Фукция для нахождения последней строки
Public Function LastRow(Optional ws As Worksheet, Optional FindRange As String) As Long
  If ws Is Nothing Then Set ws = ActiveSheet
  If FindRange = "" Then FindRange = Cells.Address
  LastRow = ws.Range(FindRange).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End Function

Sub test()

 Sheets("Sheet1").Select

  'следующая пустая строка, необходимо указать столбцы в которых будет осуществляться поиск последней заполненной строки
  n = LastRow(, "$A:$D") +  1 
   
   Range("A" & n).Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.Formula = "=VLOOKUP(""test1"",'sheet2'!$A:$B,2,FALSE)"

   Range("B" & n).Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.Formula = "=VLOOKUP(""test2"",'sheet2'!$A:$B,2,FALSE)"

   Range("C" & n).Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.Formula = "=VLOOKUP(""test3"",'sheet2'!$A:$B,2,FALSE)"

   Range("D" & n).Select
   Selection.NumberFormat = "#,##0.00"
   ActiveCell.Formula = "=VLOOKUP(""test4"",'sheet2'!$A:$B,2,FALSE)"
   
   Sheets("Sheet1").Range("A" & n & ":" & "D" & n).Select
   Selection.copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Sub test2()
  Dim myrange As Range, n As Long, x As String, y As String
  ' предполагается что в 1-й строке содержатся данные для поска (выбора)
  ' буква первого столбца, который необходимо заполнить
  x = "A"
   ' буква первого столбца, который необходимо заполнить
  y = "D"
  Sheets("Sheet1").Select
  n =  1  + Range(x & ":" & y).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  Set myrange = Range(x & n & ":" & y & n)
   myrange.NumberFormat = "#,##0.00"
  For Each icel In myrange
    icel.Value = Sheets("Sheet2").Range("$A:$B").Find(Cells( 1 , icel.Column), LookIn:=xlValues, LookAt:=xlWhole).Offset( 0 ,  1 ).Value
  Next icel
End Sub

Set FI = Range("A" & Workbooks("документ откуда нужно взять данные[color=red].xls[/color]").Sheets("страница, откуда нужно взять данные").Range("$A:$A").Find(What:="Section1", LookIn:=xlValues, LookAt:=xlWhole).Row -  1 )

icel.Value = Workbooks("документ откуда нужно взять данные[color=red].xls[/color]").Sheets("страница, откуда нужно взять данные").Range("$A1:$B5000").Find(Cells( 1 , icel.Column), After:=FI, LookIn:=xlValues, LookAt:=xlWhole).Offset( 0 ,  1 ).Value

Sub test3()
  Dim myrange As Range, FindRange As Range, n As Long, x As String, y As String, z As Long, FI As Range
   x = "A"

  y = "C"
 ' номер последнего столбца в диапазоне поска исходных данных
  z =  50 
  Sheets("Sheet2").Select
  n =  1  + Range(x & ":" & y).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  Set myrange = Range(x & n & ":" & y & n)
   myrange.NumberFormat = "#,##0.00"
  ' Определение диапазона поиска
   Set FindRange = Workbooks("Книга из которой копируем").Sheets("Sheet1").Range("A1" & ":" & "B" & z)
  
  For Each icel In myrange
    Set FI = Range("A" & FindRange.Find(Cells( 2 , icel.Column), _
    LookIn:=xlValues, SearchDirection:=xlPrevious, LookAt:=xlWhole).Row)
    
    icel.Value = FindRange.Find(Cells( 1 , icel.Column), _
    After:=FI, LookIn:=xlValues, LookAt:=xlWhole).Offset( 0 ,  1 ).Value
  Next icel
End Sub