Macro Issue copying formula down 1 row

Occasional Visitor

I have created a form which adds a line of data onto an export sheet. I am having an issue when copying down a formula it is just bringing down the value. I have tried a number of different ways to do it but its not changing my results. The current code I am using is: 

 

Private Sub CommandButton1_Click()

Dim i As Integer

i = 1

While ThisWorkbook.Worksheets("Export").Range("A" & i).Value <> ""

i = i + 1

Wend
ThisWorkbook.Worksheets("Export").Range("A" & i).Value = ThisWorkbook.Worksheets("Export").Range("A" & i).Offset(-1, 0)
ThisWorkbook.Worksheets("Export").Range("B" & i).Value = "Business Plan"
ThisWorkbook.Worksheets("Export").Range("C" & i).Value = ItemChosen.Value
ThisWorkbook.Worksheets("Export").Range("D" & i).Value = Itemcode
ThisWorkbook.Worksheets("Export").Range("E" & i).Value = ItemDetail
ThisWorkbook.Worksheets("Export").Range("F" & i).Value = ItemFY
ThisWorkbook.Worksheets("Export").Range("G" & i).Value = ItemTargetDate
ThisWorkbook.Worksheets("Export").Range("H" & i).Value = ThisWorkbook.Worksheets("Export").Range("H" & i).Offset(-1, 0)
ThisWorkbook.Worksheets("Export").Range("I" & i).Value = UProgressStatus.Value
ThisWorkbook.Worksheets("Export").Range("J" & i).Value = UProgressComment.Value
ThisWorkbook.Worksheets("Export").Range("K" & i).Value = UTargetDate.Value
ThisWorkbook.Worksheets("Export").Range("L" & i).Value = Date
ThisWorkbook.Worksheets("Export").Range("L" & i).NumberFormat = "mmmyy"
ThisWorkbook.Worksheets("Export").Range("M" & i).Value = ThisWorkbook.Worksheets("Export").Range("M" & i).Offset(-1, 0)
ThisWorkbook.Worksheets("Export").Range("N" & i).Value = "NOT REQUIRED"
ThisWorkbook.Worksheets("Export").Range("O" & i).Value = "NOT REQUIRED"
ThisWorkbook.Worksheets("Export").Range("P" & i).Value = "NOT REQUIRED"

Unload Me

End Sub

 

 

I have bolded the lines I havent got to work 

Thanks for any help

Simon

1 Reply

@Simonbrownsbiii 

Change lines such as

 

ThisWorkbook.Worksheets("Export").Range("A" & i).Value = ThisWorkbook.Worksheets("Export").Range("A" & i).Offset(-1, 0)

 

to

 

ThisWorkbook.Worksheets("Export").Range("A" & i - 1).Resize(2).FillDown