так что ли ?
Sub mak_copy_post()
For n = 5 To 10000
If Worksheets("Лист 1").Cells(n, 2) = "" Then
Worksheets("Лист 1").Cells(n, 2).Value = ActiveSheet.Cells(2, 1).Value
Worksheets("Лист 1").Cells(n, 3).Value = ActiveSheet.Cells(2, 2).Value
Worksheets("Лист 1").Cells(n, 4).Value = ActiveSheet.Cells(2, 3).Value
End If
If Worksheets("Лист 2)").Cells(n, 2) = "" Then
Worksheets("Лист 2)").Cells(n, 2).Value = ActiveSheet.Cells(4, 12).Value
Worksheets("Лист 2)").Cells(n, 3).Value = ActiveSheet.Cells(4, 13).Value
Worksheets("Лист 2)").Cells(n, 4).Value = ActiveSheet.Cells(4, 14).Value
Worksheets("Лист 2)").Cells(n, 5).Value = ActiveSheet.Cells(4, 15).Value
End If
Sheets("Лист 1").Select
Next n
End Sub