Sub AddMonth()

Dim sht1 As Worksheet 'Переменная для обращения к листу 1
Dim sht2 As Worksheet 'Переменная для обращения к листу 2

Dim rngTarget As Range 'Ячейка, куда надо вставить значение

Set sht1 = ThisWorkbook.Worksheets(1)
Set sht2 = ThisWorkbook.Worksheets(2)

Set rngTarget = sht1.Range("B1")

'Цикл поиска свободной ячейки для записи. Движение начинается с ячейки B1.
'Если она занята, то выполняется смещение на одну ячейку вправо.
'Цикл останавливается, когда мы дошли до пустой ячейки.
Do While rngTarget.Value <> ""
    Set rngTarget = rngTarget.Offset(0, 1)
Loop

'Проверка: не ушли ли мы за 6-й столбец (за столбец "F")?
If rngTarget.Column < 7 Then
    'Если не ушли за 6-й столбец, то берем значение из
    'ячейки "D4" на листе 2 и присваиваем его значению
    'ячейки rngTarget
    rngTarget.Value = sht2.Range("D4").Value
End If

End Sub