VBA macro to copy specific data onto new workbook

[ConnollyFLS]

Hi All,

Firstly, apologies for my lack of knowledge - VBA is new to me as of today.

The situation I have is as follows:

My company has a "Master Sheet", This sheet contains ALL booking information for our customers. The problem I have is that we no longer want to simply Hide data on the spreadsheet, What we are aiming to do is set a specific VBA macro to cut and paste a Row from The master sheet to a separate workbook once the job has been processed - Actioned by Typing "Complete" in a certain column on the row.

I hope I have explained this well enough, Any assistance would be greatly appreciated.

Thank you,
Tom

 

[alansidman]

Which column will hold hold the term "complete"?

 

[ConnollyFLS]

Which column will hold hold the term "complete"?

Hi alansidman Thank you for your prompt reply, the “complete” reference is in column 10
Hope this helps

kind regards
Tom

 

[alansidman]

The following code places the "Cut" row in a worksheet in the same workbook. The sheet name is "Sheet2"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s2 As Worksheet, lr As Long
    Set s2 = Sheets("Sheet2")
    If Target.Column = 10 Then
        If Target = "Complete" Then
            Target.EntireRow.Copy
            lr = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr).PasteSpecial xlPasteValues
            Target.EntireRow.Delete
        End If
    End If
End Sub

This code is to be placed in the worksheet_change event of the VBE.
The file needs to saved as macro enabled. "xlsm"

 

[ConnollyFLS]

The following code places the "Cut" row in a worksheet in the same workbook. The sheet name is "Sheet2"

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim s2 As Worksheet, lr As Long
    Set s2 = Sheets("Sheet2")
    If Target.Column = 10 Then
        If Target = "Complete" Then
            Target.EntireRow.Copy
            lr = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
            s2.Range("A" & lr).PasteSpecial xlPasteValues
            Target.EntireRow.Delete
        End If
    End If
End Sub

This code is to be placed in the worksheet_change event of the VBE.
The file needs to saved as macro enabled. "xlsm"

Thank you once again for your gem of knowledge, I will try and implement this Tomorrow and will revert back.

Have a good evening Sir
Tom