Macro Issue copying formula down 1 row

%3CLINGO-SUB%20id%3D%22lingo-sub-2307376%22%20slang%3D%22en-US%22%3EMacro%20Issue%20copying%20formula%20down%201%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2307376%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20form%20which%20adds%20a%20line%20of%20data%20onto%20an%20export%20sheet.%20I%20am%20having%20an%20issue%20when%20copying%20down%20a%20formula%20it%20is%20just%20bringing%20down%20the%20value.%20I%20have%20tried%20a%20number%20of%20different%20ways%20to%20do%20it%20but%20its%20not%20changing%20my%20results.%20The%20current%20code%20I%20am%20using%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3C%2FP%3E%3CP%3EDim%20i%20As%20Integer%3C%2FP%3E%3CP%3Ei%20%3D%201%3C%2FP%3E%3CP%3EWhile%20ThisWorkbook.Worksheets(%22Export%22).Range(%22A%22%20%26amp%3B%20i).Value%20%26lt%3B%26gt%3B%20%22%22%3C%2FP%3E%3CP%3Ei%20%3D%20i%20%2B%201%3C%2FP%3E%3CP%3EWend%3CBR%20%2F%3E%3CSTRONG%3EThisWorkbook.Worksheets(%22Export%22).Range(%22A%22%20%26amp%3B%20i).Value%20%3D%20ThisWorkbook.Worksheets(%22Export%22).Range(%22A%22%20%26amp%3B%20i).Offset(-1%2C%200)%3C%2FSTRONG%3E%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22B%22%20%26amp%3B%20i).Value%20%3D%20%22Business%20Plan%22%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22C%22%20%26amp%3B%20i).Value%20%3D%20ItemChosen.Value%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22D%22%20%26amp%3B%20i).Value%20%3D%20Itemcode%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22E%22%20%26amp%3B%20i).Value%20%3D%20ItemDetail%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22F%22%20%26amp%3B%20i).Value%20%3D%20ItemFY%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22G%22%20%26amp%3B%20i).Value%20%3D%20ItemTargetDate%3CBR%20%2F%3E%3CSTRONG%3EThisWorkbook.Worksheets(%22Export%22).Range(%22H%22%20%26amp%3B%20i).Value%20%3D%20ThisWorkbook.Worksheets(%22Export%22).Range(%22H%22%20%26amp%3B%20i).Offset(-1%2C%200)%3C%2FSTRONG%3E%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22I%22%20%26amp%3B%20i).Value%20%3D%20UProgressStatus.Value%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22J%22%20%26amp%3B%20i).Value%20%3D%20UProgressComment.Value%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22K%22%20%26amp%3B%20i).Value%20%3D%20UTargetDate.Value%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22L%22%20%26amp%3B%20i).Value%20%3D%20Date%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22L%22%20%26amp%3B%20i).NumberFormat%20%3D%20%22mmmyy%22%3CBR%20%2F%3E%3CSTRONG%3EThisWorkbook.Worksheets(%22Export%22).Range(%22M%22%20%26amp%3B%20i).Value%20%3D%20ThisWorkbook.Worksheets(%22Export%22).Range(%22M%22%20%26amp%3B%20i).Offset(-1%2C%200)%3C%2FSTRONG%3E%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22N%22%20%26amp%3B%20i).Value%20%3D%20%22NOT%20REQUIRED%22%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22O%22%20%26amp%3B%20i).Value%20%3D%20%22NOT%20REQUIRED%22%3CBR%20%2F%3EThisWorkbook.Worksheets(%22Export%22).Range(%22P%22%20%26amp%3B%20i).Value%20%3D%20%22NOT%20REQUIRED%22%3C%2FP%3E%3CP%3EUnload%20Me%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20bolded%20the%20lines%20I%20havent%20got%20to%20work%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%3C%2FP%3E%3CP%3ESimon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2307376%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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