VBA Moving rows of data between sheet to sheet

[jislandhopper]

Hello,

Hope the community can help with this one. It surrounds using VBA to cut or move a row from one sheet and pasting into another. I’ve looked online and found similar issues but nothing I found I can translate into my own problem. I have an OK understanding on excel formula but VBA is an area where I don’t know as much and trying to learn as a go. There is a lot of data and the times I have tried Excel became very slow and on the whole didn't work.

I have lines of data in sheet 1 (titled ‘Data’) relating to each job I undertake. The information runs from Column A:AU. I would like to do is type “File” into Column N which automatically moves row within A:AU into Sheet 2 (titled ‘History’)

When the information is moved into sheet 2 I would like the information to create a list where the last entry would be on the bottom of the list. Then remove the blank line in sheet 1 to bring the data together.

So in summary;
I would Type “File” in Column N sheet 1 (titled ‘Data’)
This would automatically move the line or row of data A:AU to Sheet 2 (titled ‘History’).
The row of data pasted would be at the bottom of that list in Sheet 2 (along with the other entry’s)
Sheet 1 would remove the blank row.

Hope that explains the issue, it sounds so easy as can be done in a few keystrokes but I’m making me pull my hair our trying to find a solution so any help or guidance is much appreciated,

Thanks,
Jason

 

[GWteB]

Is it possible that your Sheet2 has another tab name? "History" seems a predefined name in Excel.

 

[jislandhopper]

I had it as History. (with a stop) to get around this. Or historic would work,

 

[jislandhopper]

Or 'Historic' would work

 

[jislandhopper]

.

 

[GWteB]

Okay, this code might be a solution for you. I deliberately did not use the Worksheet_Change event, since this can cause some unforeseen problems. So when you change the value of whatever cell in column "N" nothing happens yet. However, when you select another cell and return to column "N" it checks if the value of that particular cell is "File".
The following goes in the module of your Data sheet:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Target.Count > 1 Then
        If Target.Column = Me.Range("N:N").Column Then
            If Target.Value = "File" Then
                Call MoveToHistory(argSource:=Target)
            End If
        End If
    End If
End Sub

The following goes in a standard module:

Option Explicit

Public Sub MoveToHistory(argSource As Range)

    Dim oWs         As Worksheet
    Dim raTarget    As Range

    Set oWs = Sheet2        ' <<< code name of your History sheet; change accordingly or use     Sheets("tabname")
    Set raTarget = oWs.Range("A" & oWs.Cells(oWs.Rows.Count, "A").End(xlUp).Row)

    argSource.EntireRow.Copy Destination:=raTarget
    argSource.EntireRow.Delete
    Set raTarget = Nothing
    Set oWs = Nothing
End Sub

 

[Snakehips]

@GWteB Looking at your above codes I would imagine that you would need to select a cell that already contains "File" in N for it to trigger the move.
Also, your raTarget row is always the same so will just keep overwriting the same row. So needs to be .... +1

Is there any reason not to use the Change event code such as like below, pasted into the Data sheet module?

Private Sub xxWorksheet_Change(ByVal Target As Range)
If Not Target.Column = 14 Then Exit Sub
If Target <> "File" Then Exit Sub
'***Delete 2 lines below if confirmation not required
Response = MsgBox("Do you really wish to file this row?", vbYesNo, "Just Checking!")  
If Not Response = vbYes Then Exit Sub
'***
Application.EnableEvents = False
With Sheets("Historic")  'Edit sheet name to suit
NewRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
.Range("A" & NewRow & ":AU" & NewRow) = Target.Offset(0, -13).Resize(1, 47).Value
End With
Target.EntireRow.Delete
Application.EnableEvents = True
End Sub

Hope that helps.

 

[Snakehips]

@jislandhopper Is your data all hard data rather than generated by formula? As is, either of the two above approaches will mess with any formulas below the deleted row.

 

[GWteB]

@GWteB Looking at your above codes I would imagine that you would need to select a cell that already contains "File" in N for it to trigger the move.

That's exactly what I noted.

Also, your raTarget row is always the same so will just keep overwriting the same row. So needs to be .... +1

You are right on that, my mistake ....

Option Explicit

Public Sub MoveToHistory(argSource As Range)

    Dim oWs         As Worksheet
    Dim raTarget    As Range

    Set oWs = Sheet2        ' <<< code name of your History sheet; change accordingly or use     Sheets("tabname")
    Set raTarget = oWs.Range("A" & oWs.Cells(oWs.Rows.Count, "A").End(xlUp).Row + 1)
    argSource.EntireRow.Copy Destination:=raTarget
    argSource.EntireRow.Delete
    Set raTarget = Nothing
    Set oWs = Nothing
End Sub

Is there any reason not to use the Change event code such as like below, pasted into the Data sheet module?

No, just personal preference.

 

[jislandhopper]

Thanks all your replies.

@Snakehips Most of the line is hard data with the exception of a few formula codes. It's not essential to keep the code. The sheet is more for historic records.
I tried your VBA but with no luck, if it needs the data module in the sheet I have encountered 'ambiguous name.' within this code.


@GWteB I've had a play with your code but not getting much luck. The line from sheet 1 disappears but doesn't re-appear in sheet 2.
Also 'MoveToHistory' comes up as an ambiguous name. If you change it, it returns with 'function not defined'

I'll try on a fresh sheet to make sure nothing is causing issues. Does it matter if some of the coulombs are hidden?