Move Data to Another Sheet in Next Empty Row

Microsoft Excel 2007
March 1, 2010 at 11:34:26
Specs: Windows XP
Okay, I have this spreadsheet. It is called HVAC Master Roster. I want data to move from the "Master Roster" sheet to the "Placed" sheet based on whether I mark any cell in Column J with a "P". I want the information to be cut from the row in the "Master Roster" sheet, pasted into the next empty row in the "Placed" sheet, and then I want the empty row in the "Master Roster" sheet to be deleted. All I want is the coding because I cannot, for the life of me, figure it out.
So far I have been able to cut the row and add it to the "Placed" sheet but it will not add to the next row down nor will it delete the empty cells left over in the "Master Roster" sheet. Then I managed to find the next empty row but for some reason all of the information would paste into the same row and erase the previous information. Then all of my coding stopped working completely and I had to scrap the whole workbook and start over. Please, if you have ANY advice at all, I will try anything at this point. This is for my job and I have never even looked at Macro before this week so I am a bit out of the loop with this coding stuff.

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

Report •


#1
March 1, 2010 at 12:21:13
Hi,

Here is a macro that should do what you want.

As changes made by a Macro cannot be undone with the Undo function, always make a copy of your data before running the macro.

Test this macro on duplicate data to ensure that it works as expected. As I do not have your data or your environment I cannot be sure that it will work as expected.

This macro uses the Change event which is triggered whenever a cell on the HVAC Master Roster worksheet is changed. It tests for a "P" in column J and if found moves the row to the next empty row on the Placed worksheet.

Note that due to the way that the macro finds the next empty row, all cells in column A on the Placed worksheet below the last entry, must be empty.

In Excel, open the Master Roster worksheet.
Right click the name Tab at the bottom of the sheet and select View Code.
In the Visual Basic window that opens, paste this code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = False

On Error GoTo ErrHnd

'test if changed cell is in column J (col. 10) and it contains P (or p)
If Target.Column = 10 And UCase(Target.Text) = "P" Then
    Dim rngCell As Range
    Dim rngDest As Range
    Dim strRowAddr As String
    
    'save target row address
    strRowAddr = Target.Address
    
    'find next row in destination worksheet
    Set rngDest = Worksheets("Placed"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

    'cut the source row & paste to destination
    Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("HVAC Master Roster").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub

Check that the worksheet names in the code match exactly what you have and edit if required.

From the Visual Basic window, select Save from the File Menu

Use Alt + f11 (the Alt key and function key #11 pressed together) to move back to the main Excel window

Hope this helps. If its not quite what you need please let me know.

Regards


Report •

#2
September 7, 2010 at 20:20:27
is it possible to test the same cell for different values and move them to corresponding lists? I have used your code as follows:

Sub Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = False


On Error GoTo ErrHnd

'test if changed cell is in column A (col. 1) and it contains transferred (or TRANSFERRED)
If Target.Column = 1 And UCase(Target.Text) = "TRANSFERRED" Then
Dim rngCell As Range
Dim rngDest As Range
Dim strRowAddr As String

'save target row address
strRowAddr = Target.Address

'find next row in destination worksheet
Set rngDest = Worksheets("Transferred Claims"). _
Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

'cut the source row & paste to destination
Target.EntireRow.Cut Destination:=rngDest
'remove the cut/copy range marquee
Application.CutCopyMode = False
'delete the source row
Worksheets("Open Claims").Range(strRowAddr).EntireRow.Delete _
Shift:=xlUp

End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub


I want to move "Closed" to a "Closed Claims" sheet just as I have input "transferred" to "transferred Claims" sheet and I also want to be able to "reopen" them and move them back to the "open Claims" sheet which is the main sheet I work off of. I have toyed with this a lot and can only get one test to work at a time.


Report •

#3
September 8, 2010 at 05:12:26
Hi,

This modification to the macro allows for multiple actions based on the text entered in column A on the worksheet named "Open Claims"

Option Explicit

Sub Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = False

On Error GoTo ErrHnd

'test if changed cell is in column A (col. 1)
If Target.Column = 1 Then
    'change is in column A
    Dim rngCell As Range
    Dim rngDest As Range
    Dim strRowAddr As String
    Dim strUCname As String
    Dim strWSname As String
    Dim blnWSvalid As Boolean
    
    'so test what the changed cell contains
    'and create a worksheet name if appropriate
    'start by saving an Upper Case version of the text
    strUCname = UCase(Target.Text)
    Select Case strUCname
        Case "TRANSFERRED"
            'create worksheet name
            strWSname = "Transferred Claims"
            'flag valid
            blnWSvalid = True
        Case "CLOSED"
            'create worksheet name
            strWSname = "Closed Claims"
            'flag valid
            blnWSvalid = True
        Case Else
            'not a valid action word
            'flag not valid
            blnWSvalid = False
    End Select
    
    'if the data was valid for a move to another worksheet
    'process the move
    If blnWSvalid = True Then
        'save target row address
        strRowAddr = Target.Address
        'find next row in destination worksheet
        Set rngDest = Worksheets(strWSname). _
                Range("A" & CStr(Application.Rows.Count)) _
                .End(xlUp).Offset(1, 0)
        'cut the source row & paste to destination
        Target.EntireRow.Cut Destination:=rngDest
        'remove the cut/copy range marquee
        Application.CutCopyMode = False
        'delete the source row
        Worksheets("Open Claims").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
    End If
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub

I suggest that you use data validation drop-downs in column A so that users will only be able to select valid actions - then you wouldn't have to worry about upper or lower case and there wouldn't be any typos - "Transfreed" for example.

You also say you want to be able to move cases back into the 'Open Claims' worksheet.

To do this how would you identify a case that needs to be moved.
As this code deletes cases that are moved from Open to Closed or Transferred, you would have to identify them on the Closed or Transferred worksheets.

If you used column A on those two worksheets, you could use a similar macro in each worksheet which responds to "OPEN" in column A and moves them into the 'Open Claims' worksheet.

Regards


Report •

Related Solutions

#4
September 8, 2010 at 16:52:00
you are awesome! the code worked like a champ! how are you at outlook automation? I recieve standard emails notifying me of new assignments and I would like to take the info from the email ( claim # "##A######" and a name) and put it in it's respective cells on the "Open Claims" worksheet on the file called "checksheet".

the email looks like this:

Subject: ASSIGNMENT - CLAIM# 12A345678

Body:

CLAIM NUMBER 12A345678 HAS BEEN ASSIGNED TO:
CSC: 042 CLAIM REPRESENTATIVE: 999
INSURED: DOE, JOHN

The emails come in my inbox and are automatically sent to a subfolder called "Assigned Claims"

I would like to put the claim number in the next blank line in column B and the name on that same blank line in column c and the date it is recieved in column d. Is this even possible?

Thanks for your help so far! You made my week with that macro code!


Report •

#5
September 9, 2010 at 04:32:48
Hi,

Glad to hear that it works - but as to automating Outlook, I have no experience of that.

In fact the reason I first came to this site, was that I was looking for information on automating Outlook !

Regards


Report •


Ask Question