Automatically Insert Row and Copy Formulas Down

[AndreaJean18]

I am working on a spreadsheet for my office. My goal is for the staff to use a shortcut key to insert a row and copy of the all the formulas down to the newly inserted row. I have tried VBA and macros from old posts, but I can't seem to get it to work. Let me know if you need additional information to assist me. Thank you so much.

[satputenandkumar0]

Dear AndreaJean18,

Please attach sample workbook with desired output.

[AndreaJean18]

How do I attach so that only you can see it?

[satputenandkumar0]

How do I attach so that only you can see it?

All forum readers can see your post.

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

[AndreaJean18]

Attached is the file. Under the Andrea tab, a user will add a row anywhere on the sheet and the formulas from the above row will automatically copy down. I would also like the data in the first five columns to copy down automatically as well.

[satputenandkumar0]

Dear AndreaJean18,

try below code, (for assign shortcut to code see link http://www.wiseowl.co.uk/blog/s139/s...cros-excel.htm

Please Login or Register  to view this content.

[AndreaJean18]

I get this error:

[satputenandkumar0]

Dear AndreaJean18,

You have left incomplete code there. Delete that code and try.

I have edited code in post #6 for faster result.

[AndreaJean18]

That works great! Is there also a way to copy the text down from the first five columns as well?

[satputenandkumar0]

Is there also a way to copy the text down from the first five columns as well?

you can try below also

Please Login or Register  to view this content.

[AndreaJean18]

Ok, I'm new at this. So do I replace that line, see below:


Sub insertrow()
Application.Calculation = xlCalculationManual
Dim Actrow As Long: Actrow = ActiveCell.Row
ActiveCell.EntireRow.Insert shift:=xlDown
For i = 1 To 5
Cells(Actrow, i).FillDown
Next i
For i = 6 To 85
If Cells(Actrow - 1, i).HasFormula = True Then
Range(Cells(Actrow, 1), Cells(Actrow, 5)).FillDown
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

[satputenandkumar0]

Below code is for Insert row with first five column copy down.

Please Login or Register  to view this content.

[AndreaJean18]

I got it to work. Thank you, thank you, thank you. I really appreciate your help with this.

[satputenandkumar0]

I have already posted in post #6. Please check