Solved Move Data to Another Sheet in Next Empty Row + Delete Macro

August 5, 2013 at 11:53:25
Specs: Windows XP
Hello All,

I have a spreadsheet named "Required Work". I want data to move from this sheet to another spreadsheet named "WIP" based on whether I enter any data into column G. The data entered into column G is going to be months but I would like it to move the data if any value is entered. I would like the information to be cut from the "Required Work" sheet and pasted into the next empty row in "WIP" sheet and I want the empty row in "Required Work" to be deleted.

All I am looking for is the code because I cannot figure this out. Any advice at all would be great! Thank you for your assistance I greatly appreciate it.


See More: Move Data to Another Sheet in Next Empty Row + Delete Macro

Report •


#1
August 5, 2013 at 12:18:15
✔ Best Answer
Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only a single cell in Column G has changed
   If Target.Column = 7 And Target.Cells.Count = 1 Then
     Application.EnableEvents = False
'Determine next empty Row in WIP
        nxtRw = Sheets("WIP").Range("G" & Rows.Count).End(xlUp).Row + 1
'Copy Target Row to WIP, Delete Target Row
           Range("G" & Target.Row).EntireRow.Copy _
             Destination:=Sheets("WIP").Range("A" & nxtRw)
           Range("G" & Target.Row).EntireRow.Delete
     Application.EnableEvents = True
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 5, 2013 at 17:14:25
Thank you very much. Works just like I wanted.

Is there anyway to ensure the formatting is the same as the first worksheet when cut and pasted on the 2nd worksheet?


Report •

#3
August 6, 2013 at 06:39:26
The data is not Cut and Pasted, it is Copied and Pasted and then the original row is deleted. That info can be found in the comments included in the code:

'Copy Target Row to WIP, Delete Target Row

When I run the code, all formats are Copied to WIP. What formats are not being copied for you?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 12, 2013 at 16:29:59
Never mind. I had to change some formatting on my end. Thanks again!

Report •

Ask Question