Delete columns

[scrumpyjack]

My macro below as can be seen when a condition is achieved ('ongoing' is typed in the master work sheet) in a column that line of data is moved into a work sheet called ONGOING.
When the word 'complete is typed into the master work sheet the data on that line is moved into a work sheet called 'COMPLETE' and that line that was in the ONGOING sheet is deleted.
Works perfectly as is but..
I want to remove columns in the master sheet ie move the 'Status' column that registers ongoing or complete from column 'X' to column 'U'.
So I adjusted the macro accordingly.
Works perfectly with the exception the 'ONGOING' sheet doesn't annotated that is the 'foundCase.EntireRow.Delete' function doesn't work.
Can some one tell me what else in the code needs adjusting please.
Fred







Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("X:X")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Dim bottomH As Long
Dim bottomC As Long
Dim rng As Range
Dim foundCase As Range
If Target = "ongoing" Then
bottomC = Sheets("ONGOING").Range("C" & Rows.Count).End(xlUp).Row
Set foundCase = Sheets("ONGOING").Range("C2:C" & bottomC).Find(Target.Offset(0, -16), LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCase Is Nothing Then
'Target.EntireRow.Copy Sheets("ONGOING").Range("A" & foundCase.Row)
Range("A" & Target.Row).Copy Sheets("ONGOING").Range("A" & foundCase.Row)
Range("G" & Target.Row & ":I" & Target.Row).Copy Sheets("ONGOING").Range("B" & foundCase.Row)
Range("J" & Target.Row & ":K" & Target.Row).Copy Sheets("ONGOING").Range("E" & foundCase.Row)
Range("S" & Target.Row).Copy Sheets("ONGOING").Range("G" & foundCase.Row)
'Range("V" & Target.Row).Copy Sheets("ONGOING").Range("G" & foundCase.Row)'
Range("X" & Target.Row).Copy Sheets("ONGOING").Range("H" & foundCase.Row)
Else
Range("A" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Range("G" & Target.Row & ":I" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
Range("J" & Target.Row & ":K" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
Range("S" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)
'Range("V" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "G").End(xlUp).Offset(1, 0)'
Range("X" & Target.Row).Copy Sheets("ONGOING").Cells(Rows.Count, "H").End(xlUp).Offset(1, 0)
End If
ElseIf Target = "complete" Then
bottomH = Sheets("COMPLETE").Range("H" & Rows.Count).End(xlUp).Row
Set foundCase = Sheets("COMPLETE").Range("H2:H" & bottomH).Find(Target.Offset(0, -16), LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCase Is Nothing Then
Target.EntireRow.Copy Sheets("COMPLETE").Range("A" & foundCase.Row)
Else
Target.EntireRow.Copy Sheets("COMPLETE").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End If
bottomC = Sheets("ONGOING").Range("C" & Rows.Count).End(xlUp).Row
Set foundCase = Sheets("ONGOING").Range("C2:C" & bottomC).Find(Target.Offset(0, -16), LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCase Is Nothing Then
foundCase.EntireRow.Delete
End If
End If
End Sub

[davesexcel]

Hello,

Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[scrumpyjack]

Please Login or Register  to view this content.

[scrumpyjack]

I think I have attached !!
Fred

[davesexcel]

Your code provided and your sample workbook, does not show what you are explaining in your 1st post.

You need to change your Target column to U:U and your offsets will be -13 won't they.

[scrumpyjack]

Yes correct U:U is what I am trying to achieve I understand that I had changed it so but......The delete action didn't delete.
I didn't adjust the offset I left it at -16 I will change and try again.
Thanks

Can you explain the offset statement please, whats it offsetting?
Thanks again
Fred

[scrumpyjack]

Tried that it works perfectly.
Thanks very much can you explain the offset rule.
Whats it offsetting from what
Fred

[davesexcel]

Basically,
The -13 is 13 cells from the left of target cell, 13 would be 13 cells from the right of target cell.
Select a cell in column U. Then press the left arrow key 13 times.

[scrumpyjack]

OK understand what you say but if you count 13 cells to the left of the target cell it comes in the middle of the row !
Whats that got to do with any thing? What are we trying to achieve by putting in a 13 cell offset?
Fred

[scrumpyjack]

OK see where we are coming from now its the 13th cell off set to the left that makes it the cell the target needs to be in the new sheet.
Duhhhh
Fred