General discussion

Locked

Excel automating next line

By Borg7of9 ·
I have many excel workbooks, say from 10 to 30 colums across, and many rows, and some colums are formulas and some are input fields.

Rather than always having to copy the next 10 rows or so of formulas, and having empty rows , I would like that when I complete a row of input, at the end it would automatically copy the formulas down to the next row.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by chitosunday In reply to Excel automating next lin ...

Just complete all your data down the row without the formula. Then in the first formula row, put your cursor down right which will make your cursor thinner, then double click the thin cursor. It will automatically copy the first formula down . Note that this will only be applicable to continuous data at your left. If there is a space, it will stop there.

Collapse -

by Borg7of9 In reply to

Poster rated this answer.

Collapse -

by chitosunday In reply to Excel automating next lin ...

Just complete all your data down the row without the formula. Then in the first formula row, put your cursor down right which will make your cursor thinner, then double click the thin cursor. It will automatically copy the first formula down . Note that this will only be applicable to continuous data at your left. If there is a space, it will stop there.

Collapse -

by Borg7of9 In reply to

Poster rated this answer.

Collapse -

by Borg7of9 In reply to Excel automating next lin ...

I tried this, maybe I didnt do it right, but it didnt work. However, even if it did work, there is data between cells with formulas.

I can simply copy the forulas, but the person entering the data is not good with excel, I wanted to make say a macro, where it would simply copy the formulas down one. I just dont know how to do it, or if there is a better way

Collapse -

by chitosunday In reply to Excel automating next lin ...

If you want a macro put this in a the sheet code by right clicking the tab sheetname below left.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xrange As Range
If Target.HasFormula Then
Application.EnableEvents = False
If Target.Column > 1 Then
Set xrange = Target.Offset(0, -1).End(xlDown).Offset(0, 1)
Range(Target, xrange).FillDown
Application.EnableEvents = True
End If
End If
End Sub

Collapse -

by sir.gsm In reply to Excel automating next lin ...

I think what u need is, after u input the data in a row, u want that excel will automatically creates another row with the same formula... until you finished inputting?
And if you dont want to add another data to add in a row, excel will not create a row with formula in it?

I dont think this can be create but, chitosunday is right just use the FILL HANDLE to copy the formulas for each column. Lock these formula so that no body could erase or edit this formula of yours.

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums