Automatically add new row

V

vwhungsuriya

Hi All,

I am working with a couple of sheets in a workbook. In the main sheet
add information and often I add new rows. The other sheets are linke
to this main sheet. I know there must be a way such that a new row i
added in the other sheets when I add a row in the main sheet. Eve
better if it would be possible to have formulas and links automaticall
copied in as well.
I am sort of a beginner and I am wondering if you might have an
suggestions.

Thanks
 
B

Bob Phillips

Inserting a row is not a trappable event, so you cannot do it automatically.

You could have a button that you click that runs a macro to insert a row on
all sheets.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
V

vwhungsuriya

Hi Bob,

Thank you for your reply.

It's too bad that it can't be done. Still it may be interesting wit
the button. Can you give me more details on that? It has to insert row
relative to the rows added in the main sheet.

The other sheets are linked to the main sheet. When the row is adde
can one do it in such a way that the links are also in there?

/Jom
 
B

Bob Phillips

Hi Jom,

here is an example macro

Sub AddRows()
Dim numRows
Dim nRow As Long
Dim sh As Worksheet

nRow = ActiveCell.Row
numRows = InputBox("How many rows to add?")
If numRows <> "" Then
For Each sh In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
sh.Cells(nRow, "A").Resize(numRows, 1).EntireRow.Insert
Next sh
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top