Auto insert/delete on other sheet
 

I have two sheets in my workbook, sheet1 and sheet2.
Is it possible to create some sort of function or macro that can
automatically delete/insert the same row in sheet2 if I delete/insert
row(s) in sheet1 ?
For example, if I Insert Row 15 in sheet1 and it also will insert Row
15 in sheet2, Delete Row 20 in sheet1 and it will delete Row 20 in
sheet2.
I try to use event change, but don't know how to combine it with onkey
{insert/delete).
Please help.
Thanks

Auto insert/delete on other sheet
 

Click on the sheet tab for sheet2. Hold down the Ctrl key and click on the
Sheet Tab for Sheet1. Now make you insertions and deletions. Now right
click on one of the sheet tabs and select ungroup.

Changes made in group mode happen to all grouped sheets.

--
Regards,
Tom Ogilvy

"broogle" wrote in message
oups.com...
I have two sheets in my workbook, sheet1 and sheet2.
Is it possible to create some sort of function or macro that can
automatically delete/insert the same row in sheet2 if I delete/insert
row(s) in sheet1 ?
For example, if I Insert Row 15 in sheet1 and it also will insert Row
15 in sheet2, Delete Row 20 in sheet1 and it will delete Row 20 in
sheet2.
I try to use event change, but don't know how to combine it with onkey
{insert/delete).
Please help.
Thanks

Auto insert/delete on other sheet
 

Hi Tom !
It works !! Thank you.
Is it possible to lock this grouping, just in case someone will
accidently select ungroup?
Thanks again Tom.

Auto insert/delete on other sheet
 

Hi Tom.
Sorry this is not what I really want.
What I mean was the automatic update is only for delete/insert only,
not for everything.
Because some data in sheet2 still need independent.
Thanks

Auto insert/delete on other sheet
 

I don't think you can lock it, you might be able to write code in the
activate event of sheet1 to reestabish it.

Just be aware that almost any changes you make to sheet1 will happen to
sheet 2 also.

Right click on the sheet tab of Sheet1 and select view code. Put in this
code:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
Worksheets(Array("Sheet1", "Sheet2")).Select
Application.EnableEvents = True
End Sub

In the Thisworkbook module (in the vbe (Alt+F11), for you workbook, double
click on the ThisWorkbook entry and in the module put in this code)

Private Sub Workbook_Open()
Application.EnableEvents = False
Worksheets(Array("Sheet1", "Sheet2")).Select
Application.EnableEvents = True
End Sub

You could repeat the first code for Sheet2 if you never want to be able to
go to sheet2 but always had it grouped Then you could run code like this
to get to it yourself

Sub GotoSheet2()
Application.EnableEvents = False
worksheets("Sheet2").Activate
Application.EnableEvents = True
End Sub



--
Regards,
Tom Ogilvy



"broogle" wrote in message
ups.com...
Hi Tom !
It works !! Thank you.
Is it possible to lock this grouping, just in case someone will
accidently select ungroup?
Thanks again Tom.

Auto insert/delete on other sheet
 

So forget my code based on your follow up post.

There is not specific event associated with inserting and deleting rows. In
later versions of excel, inserting or deleting a row triggers the change
event, but so does a lot of other things. The target would be the row
address, but it would be the same if you just cleared a row and it doesn't
distinguish between inserting and deleting.

Nonetheless, since you know the layout of your sheet, maybe you can use that
information to differentiate and react to a row deletion or insertion.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
I don't think you can lock it, you might be able to write code in the
activate event of sheet1 to reestabish it.

Just be aware that almost any changes you make to sheet1 will happen to
sheet 2 also.

Right click on the sheet tab of Sheet1 and select view code. Put in this
code:

Private Sub Worksheet_Activate()
Application.EnableEvents = False
Worksheets(Array("Sheet1", "Sheet2")).Select
Application.EnableEvents = True
End Sub

In the Thisworkbook module (in the vbe (Alt+F11), for you workbook,
double
click on the ThisWorkbook entry and in the module put in this code)

Private Sub Workbook_Open()
Application.EnableEvents = False
Worksheets(Array("Sheet1", "Sheet2")).Select
Application.EnableEvents = True
End Sub

You could repeat the first code for Sheet2 if you never want to be able to
go to sheet2 but always had it grouped Then you could run code like this
to get to it yourself

Sub GotoSheet2()
Application.EnableEvents = False
worksheets("Sheet2").Activate
Application.EnableEvents = True
End Sub



--
Regards,
Tom Ogilvy



"broogle" wrote in message
ups.com...
Hi Tom !
It works !! Thank you.
Is it possible to lock this grouping, just in case someone will
accidently select ungroup?
Thanks again Tom.

Auto insert/delete on other sheet
 

Hi Tom,
Is it possible to limit the action only for delete/insert ?
I don't want all the changes in sheet1 being updated to sheet2.
Thanks

Auto insert/delete on other sheet
 

No, not using grouping.

--
Regards,
Tom Ogilvy

"broogle" wrote in message
oups.com...
Hi Tom,
Is it possible to limit the action only for delete/insert ?
I don't want all the changes in sheet1 being updated to sheet2.
Thanks

Auto insert/delete on other sheet
 

Do you mean delete/insert in Excel 2000 will not trigger the change
event?

Auto insert/delete on other sheet
 

Frankly, I can't remember whether it does or not and I don't have a copy
handy to test with. I know it doesn't do it in xl97. I tested it in
xl2002, so I know it does there.

Easiest is to test it

in a new blank workbook, put this into sheet1.


Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub

Then delete a row. Then insert a row.

If you do get a message, it does, conversely if you don't get a message, it
doesn't. <g

--
Regards,
Tom Ogilvy

"broogle" wrote in message
oups.com...
Do you mean delete/insert in Excel 2000 will not trigger the change
event?

Auto insert/delete on other sheet
 

It does.
Thanks a lot Tom, I will try to work it out from here.
God bless.