Deleted row reference error

[helpless]

Sheet 2 in the same workbook shows a refernce error in my data table, when
sheet 1 (which is my data source for sheet 2) has a row deleted. I can
update my data table in sheet two by creating two workbooks, however I want
to update sheet 2 in the same workbook. My workbook contains only text no
calculations.

 

[Joel]

First, your workbook does have calulations because cell references (=A5) is a
caculation. the workbook is doing what it is suppose to do when the row is
eliminated by making any references an ERROR. What do you want to happen?
Remove any references to deleted cells?

 

[helpless]

you are right about the calculations. let me try this question, using two
workbooks with the same information, it allows me to change information, add
or delete rows, and/or add or delete columns in workbook 1. save changes.
when workbook 2 is opened, it asks for update approval and the changes are
made without an error.

i know this is possible with two workbooks, but how can it be done in the
same workbook, with different sheets?

 

[Joel]

This is what I think is happening. A reference in a 2nd workbook doesn't
change after a row is deleted. This may be a problem in some cases because
the data will be referencing the data in the next row, not the original data.

To solve the problem in your work book you may want to perform a copy rather
than a delete. Copy all the rows after the row you up one row. For example

DeleteRow = 20
Rows((DeleteRow + 1) & ":" & Rows.Count).copy _
Destination:=Rows(DeleteRow)

Rows.Count is the last row on the worksheet = 65536. You may want to use a
different last row to speed up the code. Copying this many rows will take a
little time.