Hello,

I have an Excel workbook with many worksheets. The first worksheet (Sheet1)
will be need to be referenced in all other worksheets. This has been
straight-forward until i added a new row to the first worksheet. How could I
setup the workbook such that if I change/add/remove row from Sheet1, it will
do the same to the other worksheets without overwriting rows that are
appended below the referencing cells?

Please see example below. Thank you for any help and sorry if the
illustration below is overkill. I just wanted to make it clear as possible.

Brandon

To illustate, I have Sheet1 as follows:

Apple
Boat
Carpet
Dog

And, Sheet2 with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Event
Flag

And, Sheet3 again with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Giraffe
Hippo

Now, if I remove rows in Sheet1 to be:

Apple
Dog

Then, I'd like to have Sheet2 & Sheet3 to be, respectively:

(Apple)
(Dog)
Event
Flag

(Apple)
(Dog)
Giraffe
Hippo

Also, in addition, if I add rows to Sheet1 to be:

Apple
Boat
Carpet
Dog
Elephant

Then, Sheet2 & Sheet3 should be as follows:

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Event
Flag

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Giraffe
Hippo

Rather than talk in terms of deleting rows, I would refer to deleting data.
If I delete Boat and Carpet from sheet one and I want to delete it from all
sheets with all associated data on those rows, it becomes easier.

Sub delredund()
Dim sh As Worksheet, c As Range
For Each sh In ActiveWorkbook.Sheets
Set c = Cells.Find("Boat", LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Set c = Cells.Find("Carpet"), LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Next
End Sub

Code is untested.


"Brandon Arnieri" wrote:

Hello,

I have an Excel workbook with many worksheets. The first worksheet (Sheet1)
will be need to be referenced in all other worksheets. This has been
straight-forward until i added a new row to the first worksheet. How could I
setup the workbook such that if I change/add/remove row from Sheet1, it will
do the same to the other worksheets without overwriting rows that are
appended below the referencing cells?

Please see example below. Thank you for any help and sorry if the
illustration below is overkill. I just wanted to make it clear as possible.

Brandon

To illustate, I have Sheet1 as follows:

Apple
Boat
Carpet
Dog

And, Sheet2 with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Event
Flag

And, Sheet3 again with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Giraffe
Hippo

Now, if I remove rows in Sheet1 to be:

Apple
Dog

Then, I'd like to have Sheet2 & Sheet3 to be, respectively:

(Apple)
(Dog)
Event
Flag

(Apple)
(Dog)
Giraffe
Hippo

Also, in addition, if I add rows to Sheet1 to be:

Apple
Boat
Carpet
Dog
Elephant

Then, Sheet2 & Sheet3 should be as follows:

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Event
Flag

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Giraffe
Hippo

The addition of rows could be done with a similar loop, you would just have
to tell it where to Insert (and that is the key word) the rows.

"Brandon Arnieri" wrote:

Hello,

I have an Excel workbook with many worksheets. The first worksheet (Sheet1)
will be need to be referenced in all other worksheets. This has been
straight-forward until i added a new row to the first worksheet. How could I
setup the workbook such that if I change/add/remove row from Sheet1, it will
do the same to the other worksheets without overwriting rows that are
appended below the referencing cells?

Please see example below. Thank you for any help and sorry if the
illustration below is overkill. I just wanted to make it clear as possible.

Brandon

To illustate, I have Sheet1 as follows:

Apple
Boat
Carpet
Dog

And, Sheet2 with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Event
Flag

And, Sheet3 again with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Giraffe
Hippo

Now, if I remove rows in Sheet1 to be:

Apple
Dog

Then, I'd like to have Sheet2 & Sheet3 to be, respectively:

(Apple)
(Dog)
Event
Flag

(Apple)
(Dog)
Giraffe
Hippo

Also, in addition, if I add rows to Sheet1 to be:

Apple
Boat
Carpet
Dog
Elephant

Then, Sheet2 & Sheet3 should be as follows:

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Event
Flag

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Giraffe
Hippo

Correct typo:

Set c = Cells.Find("Carpet", LookIn:=xlValues)


"JLGWhiz" wrote:

Rather than talk in terms of deleting rows, I would refer to deleting data.
If I delete Boat and Carpet from sheet one and I want to delete it from all
sheets with all associated data on those rows, it becomes easier.

Sub delredund()
Dim sh As Worksheet, c As Range
For Each sh In ActiveWorkbook.Sheets
Set c = Cells.Find("Boat", LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Set c = Cells.Find("Carpet"), LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
Next
End Sub

Code is untested.


"Brandon Arnieri" wrote:

Hello,

I have an Excel workbook with many worksheets. The first worksheet (Sheet1)
will be need to be referenced in all other worksheets. This has been
straight-forward until i added a new row to the first worksheet. How could I
setup the workbook such that if I change/add/remove row from Sheet1, it will
do the same to the other worksheets without overwriting rows that are
appended below the referencing cells?

Please see example below. Thank you for any help and sorry if the
illustration below is overkill. I just wanted to make it clear as possible.

Brandon

To illustate, I have Sheet1 as follows:

Apple
Boat
Carpet
Dog

And, Sheet2 with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Event
Flag

And, Sheet3 again with cells referencing Sheet1 in parentheses:

(Apple)
(Boat)
(Carpet)
(Dog)
Giraffe
Hippo

Now, if I remove rows in Sheet1 to be:

Apple
Dog

Then, I'd like to have Sheet2 & Sheet3 to be, respectively:

(Apple)
(Dog)
Event
Flag

(Apple)
(Dog)
Giraffe
Hippo

Also, in addition, if I add rows to Sheet1 to be:

Apple
Boat
Carpet
Dog
Elephant

Then, Sheet2 & Sheet3 should be as follows:

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Event
Flag

(Apple)
(Boat)
(Carpet)
(Dog)
(Elephant)
Giraffe
Hippo