Relocating some of the rows while keeping original IDs in both sheets

Finally, I have managed to relocate certain rows from Sheet1 to Sheet2 (Zeile = Row).

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.

  • Can I import multiple text files into one excel sheet?
  • Extract the last substring from a cell
  • Excel Date column returning INT using EPPlus
  • How to remove conditional formatting in a row using Apache POI?
  • VBA - How can I give an error code based on the sum of my textbox values ina user form?
  • Paste Special Not Working more than 1 time: VBA
  • 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 Sheet1 to 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 8 in Sheet1, which will now be row 7 in Sheet1, will change its ID from 3 to 2.

    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?

  • Speed up excel formatting vba code?
  • If cell equals 0 then change that value to the one from adjacent cell
  • Unable to access XLSX object when using js-xlsx library
  • scraping and need to access elements from child window
  • Finding the median of a range of values selected using vlookup
  • IRR library is only good if the pay period and compound period is in years (Engineering Economy)
  • MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.