Excel - VBA - Deleting rows in one sheet - should delete subsequent row in another sheet

[umeshbanga]

Hi

I have one workbook. there are two worksheets

In Sheet1 the first column (from row a5) have unique identifier (ID) for each row.

The content of these rows are copied across to sheet 2.

What I want is when I delete any particular row in Sheet 1 then it should delete the same row in sheet 2. The identifier would be the first column of sheet 1.

Is it possible? Can I please get a code for this?


Regards

Umesh Banga

 

[Tet Htut Naing]

Hi,
Here I put macro for you. This macro is created by someone else, not me. But I don't remember his name. So, please thank him in your mind.
You may now how to run it.
Just for incase, the macro is for 4 Sheets, rename them before using, in accordance with your sheet names.
Htut




Option Explicit


'>>>> Modify these names to suit your sheet names <<<<
Const sSHEET1NAME = "Sheet1"
Const sSHEET2NAME = "Sheet2"
Const sSHEET3NAME = "Sheet3"
Const sSHEET4NAME = "Sheet4"
Sub DeleteLine()
' Delete a line in all staff worksheets
'
Dim rR As Range
Dim lR As Long
Dim wsWS As Worksheet
Dim mbaAnsw As VbMsgBoxResult

lR = ActiveCell.Row
mbaAnsw = MsgBox("Do you want to delete the employee: " & Cells(lR, 3) & " at current line?", _
Buttons:=vbOKCancel + vbQuestion, _
Title:="Delete Row " & Cells(lR, 2))

If mbaAnsw = vbOK Then
For Each wsWS In Worksheets
With wsWS
' only delete from the four staff sheets
If .Name = sSHEET1NAME Or .Name = sSHEET2NAME Or _
.Name = sSHEET3NAME Or .Name = sSHEET4NAME Then
.Cells(lR, 1).EntireRow.Delete
End If
End With
Next wsWS
End If
End Sub

 

[umeshbanga]

This does not work. The rows i have are in table format and both sheets are protected. I know how to unprotect them in code but how to select multiple ranges in two different sheets with two tables - cannot work it out.

Hi,
Here I put macro for you. This macro is created by someone else, not me. But I don't remember his name. So, please thank him in your mind.
You may now how to run it.
Just for incase, the macro is for 4 Sheets, rename them before using, in accordance with your sheet names.
Htut




Option Explicit


'>>>> Modify these names to suit your sheet names <<<<
Const sSHEET1NAME = "Sheet1"
Const sSHEET2NAME = "Sheet2"
Const sSHEET3NAME = "Sheet3"
Const sSHEET4NAME = "Sheet4"
Sub DeleteLine()
' Delete a line in all staff worksheets
'
Dim rR As Range
Dim lR As Long
Dim wsWS As Worksheet
Dim mbaAnsw As VbMsgBoxResult

lR = ActiveCell.Row
mbaAnsw = MsgBox("Do you want to delete the employee: " & Cells(lR, 3) & " at current line?", _
Buttons:=vbOKCancel + vbQuestion, _
Title:="Delete Row " & Cells(lR, 2))

If mbaAnsw = vbOK Then
For Each wsWS In Worksheets
With wsWS
' only delete from the four staff sheets
If .Name = sSHEET1NAME Or .Name = sSHEET2NAME Or _
.Name = sSHEET3NAME Or .Name = sSHEET4NAME Then
.Cells(lR, 1).EntireRow.Delete
End If
End With
Next wsWS
End If
End Sub