command button vba to copy formula from cell above

**KK1234** · 02-05-2014, 04:29 PM

Hi,
I have sheet1 having two formulas.
Ideally I can copy and paste the formula to the entire column,
However this then populates , say 3000 cells unnecessarily which slows down excel performance or increases size of the file.

I am looking for a vba ( this is used to open userform)
To include copy formula from filled cell above to next empty row
( cells will be in column K & L)

So every-time userform is opened, the formula is copied to next empty row.

Any help please

kind regards

Private Sub CommandButton1_Click()
UserForm1.Show
'Include here to copy and paste
End Sub

**Tinbendr** · 02-07-2014, 05:12 PM

something like

UserForm1.Show
'Include here to copy and paste
Dim WS As Worksheet
Dim NextEmptyRow As Long

Set WS = ActiveWorkbook.Worksheets(1)

With WS
    NextEmptyRow = .Cells(.Rows.Count, "K").End(xlUp).Row + 1
    
    .Range("K" & NextEmptyRow).Formula = "=sum(A" & NextEmptyRow & " + B" _
        & NextEmptyRow & ")"

    .Range("L" & NextEmptyRow).Formula = "=sum(A" & NextEmptyRow & " + B" _
        & NextEmptyRow & ")"
End With

**KK1234** · 02-08-2014, 05:32 PM

Hi Tinbendr,
Thanks for your reply.
I tried the code , but it does not seem to work.
May be I am making a mistake somewhere.

The formula I am using are ( in K say row 9)

=IF(A9="","",IF(H9=1,A9+1/12,IF(H9=2,A9+1/4,IF(H9=3,A9+1,IF(H9=4,A9+5,IF(H9=5,A9+28,"Urgency?"))))))

In L ( say in row 9 again)

=IF(A9="","",IF(K9<X9,"past PDC",""))

Can you guide me further please.
I have clicked * to thank you for offering me your valuable help.
Kind regards

**Tinbendr** · 02-08-2014, 06:06 PM

Uhhh, OK, plan B

Dim WS As Worksheet
Dim NER As Long

Set WS = ActiveWorkbook.Worksheets(1)

With WS
    NER = .Cells(.Rows.Count, "K").End(xlUp).Row

    .Range("K" & NER).Copy
    .Range("K" & NER + 1).PasteSpecial xlPasteFormulas
    
    .Range("L" & NER).Copy
    .Range("L" & NER + 1).PasteSpecial xlPasteFormulas
    
    Application.CutCopyMode = False
End With

**KK1234** · 02-08-2014, 07:29 PM

Hi TinBendr,
Perfect solution.
Much appreciated.
Once again thanks for a great solution.
Kind regards

command button vba to copy formula from cell above

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

command button vba to copy formula from cell above

Re: command button vba to copy formula from cell above

Re: command button vba to copy formula from cell above

Re: command button vba to copy formula from cell above

Re: command button vba to copy formula from cell above

Thread Information

Users Browsing this Thread

Similar Threads

copy cell value with addition of desired number with command button control

[SOLVED] macro for command button to copy cell content to other cells

copy and paste data from one worksheet cell to another using command button

Copy cell value to list with command button click

Command Button to copy conetents of one cell to another

Bookmarks

Bookmarks

Posting Permissions