Need to insert rows with formulas

[sachi1982]

Hi All,

In my work sheet i need to add 100 rows with formulas.

for eg. i want to insert 100 rows from row 300 and i want to those 100 rows to inherit row 299 formulas. this template got columns from A to Q and need to copy down all these formulas.

Could you kindly help. I have very limited knowledge in macros. so appreciate if you can give me the whole code.

cheers,
S

 

[BrianJN1]

If there is nothing beyond Col Q then we can simply select the Row 299 and copy that down the range of rows 300:400 (maybe I should have stopped at 399).
Try this:

Sub Multi_Rows()
'
' Multi_Rows Macro
'

'

    Rows("299:299").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("300:400").Select
    ActiveSheet.Paste
    
End Sub

 

[FDibbins]

Don't see why you need VBA or anything else fancy for this.

You could either convert to a Table, or just highlight the last row with formulas, and either dbl-click the fill handle, or just drag down

 

[sachi1982]

Thanks BrianJN1.

However, i need to insert the 100 rows as well. so i need to do 2 things and this need to be repeated if needed.
1. add 100 rows to the worksheet
2.copy all the formals for those inserted rows.

i am going to add a button to execute this, so that if i need more rows each time i can press this button and get 100 rows each time.

please help .

 

[jolivanes]

Maybe

Sub sachi1982
With Range("A300:Q300")
    .AutoFill Destination:=.Resize(100), Type:=xlFillCopy    '<-----Change the 100 to whatever you want/need
End With
End Sub

Note that the to be copied Range is hard coded. If that needs changing, let us know.

 

[BrianJN1]

I take then that we are going to have to provide an insertion point, eg, At Row 100, 250, ...
Are you always going to need to insert 100 rows? We could take that into account at the same time.

 

[jolivanes]

With this you should be able to keep on clicking until you run out of rows.

Sub From_Last_Used_Row()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(lr, 1), Cells(lr, 17))
    .AutoFill Destination:=.Resize(100), Type:=xlFillCopy    '<-----Change the 100 to whatever you want/need
End With
End Sub

Or, as Brian suggested

Sub From_Last_Used_Row_A()
Dim lr As Long, a As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = InputBox("How many extra rows do you want filled?", "Rows to fill with formulae")
With Range(Cells(lr, 1), Cells(lr, 17))
    .AutoFill Destination:=.Resize(a + 1), Type:=xlFillCopy
End With
End Sub

 

[sachi1982]

ooh thank you so much. one small problem. it looks like it is copying down what ever in the last used row. i got one column that got manual input. so is it possible to copy down only the formulas.
again this is so much help. Thanks again.

 

[BrianJN1]

Sachi,
I am giving you a code which does as I offered. You get two message boxes. The first asks for the insertion point, eg row 3, and then How many rows to insert, eg 10.
The macro selects Row 2 and copies it. It then selects and pastes from Row 3 for the next 10 rows.

Sub Insert_rows()
Dim StartAt As Long
Dim Many_Rw As Long
StartAt = InputBox("Insert at Row:")
Many_Rw = InputBox("How many rows:")

Rows(StartAt - 1 & ":" & StartAt - 1).Copy
Rows(StartAt & ":" & StartAt + Many_Rw).Select
    Selection.Insert Shift:=xlDown
    ActiveSheet.Paste

End Sub

Any problems with this?

Ah! I just saw your reply above. What column(s) do you NOT want formulae? That can be arranged in either my code or jolivanes.

 

[jolivanes]

You should have mentioned all of this in your first post so people don't waste time on code that is not what is required.
Read your last post again and see if there is anything in it that should be explained. Hint" "I got one column that got manual input"