SIMULTANEOUSLY adding rows in two sheets

[Guest]

I use Excel 2003. I want adding rows in one sheet the some number of rows to
be added in other sheet but automatically. I mean, if I add two rows between
33 and 34 row in sheet 1 the some number of rows to be add in sheet 2 between
33 and 34 row.

Thank you in advance for your help.

Radoslav

 

[Frank Kabel]

Hi
select both sheets before doing this (hold down the CTRL key while
selecting the sheets)

 

[KL]

Radoslaw,

try this:

Sub InsertRows()
Dim myWS As Variant
Application.ScreenUpdating = False
myWS = Array("Sheet1", "Sheet2", "Sheet3")
Sheets(myWS).Select
Rows(5).Insert Shift:=xlDown
Sheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

Regards,
KL

 

[Guest]

Dear KL,

Many thanks for your assistance. Unfortunately the code you have sent me is
not exactly what I was looking for.

It’s what you have sent me.

Sub InsertRows()
Dim myWS As Variant
Application.ScreenUpdating = False
myWS = Array("Sheet1", "Sheet2", "Sheet3")
Sheets(myWS).Select
Rows(5).Insert Shift:=xlDown
Sheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

Most probably I wasn’t clear in my problem explanation. Let me try again.

Imagine we are working in sheet1 and you insert two rows somewhere into the
sheet just using the tool bar – Insert/rows. I need automatically the same
number of rows with the same address to be inserted into sheet2.

Radoslav

 

[KL]

Radoslav,

In that case, I don't think your task can be automated, so I would recommend
you use Frank's advise to manually select the sheets (holding Ctrl key) and
then inserting the rows.

The code I sent to you is designed for situations where you need to control
user's input and normally goes together with Sheet protection and RowsInsert
and/or RowsDelete buttons with macros assigned. Thus I totally control the
row input. The only way for the user to insert rows is to use my RowInsert
button, which would trigger my code and the code will take care of 1)
unprotecting the relevant sheets, 2) selecting them, 3) inserting the number
of rows specified by the user in the area where I allow to do so, 4)
reparing formulae where necessary and 5) protecting the sheets again.

Regards,
KL