0

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.

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?

Qralnaq
  • 73
  • 1
  • 7
  • Don't link to an old post. Put your relocation code that works here. – Chrismas007 Nov 15 '16 at 16:00
  • Is the pasting in a separate sheet? If so, copy and paste the values – Nathan_Sav Nov 15 '16 at 16:05
  • @Nathan_Sav I don’t know if you were able to see the changes I’ve just made in reaction to Christmas007’s comment. Do you want to tell me that there is no other way than solving this issue manually? – Qralnaq Nov 15 '16 at 16:11
  • just musing.......you could paste values to sheet2, then do your id formula as something along max of ids in sheet1 < row + count in sheet2 < row – Nathan_Sav Nov 15 '16 at 16:19
  • Do the formulas HAVE to come over to Sheet 2? Just use `.PasteSpecial xlValues` – Chrismas007 Nov 15 '16 at 16:35
  • If that will do the job: Yes, they have to come over to `Sheet2`. I will try it later. – Qralnaq Nov 15 '16 at 16:37
  • @Chrismas007 sheet1 will then alter as they are deleted then, please see my previous post – Nathan_Sav Nov 15 '16 at 17:32

0 Answers0