Relocating some of the rows while keeping original IDs in both sheets
Finally, I have managed to relocate certain rows from
Private Sub Worksheet_Change(ByVal Target As Range) Dim Zeile As Long Set Target = Intersect(Target, Range("J6:J1006")) If Target Is Nothing Then Exit Sub If Target = "C" Then Zeile = Target.Row Application.Union(Range(Cells(Zeile, 1), Cells(Zeile, 10)), _ Range(Cells(Zeile, 13), Cells(Zeile, 16))).Copy _ Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Target.EntireRow.Delete End If End Sub
Now, there is another problem.
In the first column of
Sheet1, IDs for different projects are calculated based on the following formula:
=IF(NOT(ISEMPTY(G6)),ROW(A6)-6,"") =IF(NOT(ISEMPTY(G7)),ROW(G7)-6,"") =IF(NOT(ISEMPTY(G8)),ROW(G8)-6,"") ...
When relocating a certain row from
Sheet2, e.g. row
7, which has, according to the formula, ID number
2, it will have a different ID when being relocated to
Sheet2 and the former row
Sheet1, which will now be row
Sheet1, will change its ID from
What I want to accomplish is that the IDs will always stay the same.
I know that this could be accomplished by writing static values as ID numbers, but I am not allowed to do so. These formulas are mandatory.
So: Is there any way to accomplish this?