Checkbox - True - Moves Whole Row To Another Sheet

[JJFabEngineering]

Hi Guys,

New the threads, so thought I'd give it a go.

I'm stuck, I'm creating a workbook that we use for our tender submissions.
What I now want to do is when a check box is ticked i want to move that row to another page (not the whole row - i.e. only move a5,b5,c5 across to another worksheet). Once moved it will appear on the new sheet and when i tick the next job won (which may be 5 rows down_ that will populate directly under the first lot of information (so there are no gaps).

I am happy to email this workbook to someone to have a look at.
I would prefer it not to be a macro book but open to ideas if required.

See attached images


Example.
Sheet - QUOTE SENT

Titles
A4 = Job Address
B4 = Builder / Client
C4 = Type (Drop Down List)

Data
A5 - A1000 = Job Addresses
B5 - B1000 = Builder or Client name
C5 - C1000 = Type (Fab, Fab & Install, Install Only)

I need the above to move over to the JOBS WON Sheet and fall into order (if possible) of which was ticked first etc......

Is this doable ?

Thanks

 

[MikeVol]

Hi From Ukraine @JJFabEngineering! Remove all your Quote Sent worksheet checkboxes from column L. Paste the following code into the Quote Sent Worksheet Module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lr As Long, mycell As Range
    If Target.Cells.Count > 1 Then Exit Sub
    lr = ThisWorkbook.Worksheets("Data").UsedRange.Row + ThisWorkbook.Worksheets("Data").UsedRange.Rows.Count - 1

    If Not Intersect(Target, Range("L5:L" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        Target.Font.Name = "Marlett"

        If Target = "a" Then
            Target = ""
            Set mycell = ThisWorkbook.Worksheets("Data").Range("A5:C" & lr).Find(Me.Cells(Target.Row, 1).Value)
            ThisWorkbook.Worksheets("Data").Rows(mycell.Row).Delete Shift:=xlUp
        Else
            Target = "a"

            With ThisWorkbook.Worksheets("Data")
                .Cells(lr + 1, 1) = Me.Cells(Target.Row, 1)
                .Cells(lr + 1, 2) = Me.Cells(Target.Row, 2)
                .Cells(lr + 1, 3) = Me.Cells(Target.Row, 3)
            End With

        End If

        Target.Offset(0, 1).Activate
    End If

End Sub

Use it for your health! Good luck.

 

[JJFabEngineering]

Hi From Ukraine @JJFabEngineering! Remove all your Quote Sent worksheet checkboxes from column L. Paste the following code into the Quote Sent Worksheet Module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lr As Long, mycell As Range
    If Target.Cells.Count > 1 Then Exit Sub
    lr = ThisWorkbook.Worksheets("Data").UsedRange.Row + ThisWorkbook.Worksheets("Data").UsedRange.Rows.Count - 1

    If Not Intersect(Target, Range("L5:L" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        Target.Font.Name = "Marlett"

        If Target = "a" Then
            Target = ""
            Set mycell = ThisWorkbook.Worksheets("Data").Range("A5:C" & lr).Find(Me.Cells(Target.Row, 1).Value)
            ThisWorkbook.Worksheets("Data").Rows(mycell.Row).Delete Shift:=xlUp
        Else
            Target = "a"

            With ThisWorkbook.Worksheets("Data")
                .Cells(lr + 1, 1) = Me.Cells(Target.Row, 1)
                .Cells(lr + 1, 2) = Me.Cells(Target.Row, 2)
                .Cells(lr + 1, 3) = Me.Cells(Target.Row, 3)
            End With

        End If

        Target.Offset(0, 1).Activate
    End If

End Sub

Use it for your health! Good luck.

Anychance i can email you my form and you help me physically on the form itself i had trouble inputting this code

 

[MikeVol]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone. Thanks.

 

[JJFabEngineering]

Tender Bible New.xlsx

 

[MikeVol]

Hi @JJFabEngineering. If I understand you correctly, here is an example file. The code will work correctly if the selected row is filled with data, namely, column A must have a unique value. Look at the file and play with it. I think you can figure it out on your own.
20.05.2024_MrExB (Checkbox - True - Moves Whole Row To Another Sheet)
Good luck.

 

[JJFabEngineering]

Thanks Legend Works perfect. Is there a way of having a way of changing the rows once they are won to ascend or descend through using dates?

 

[MikeVol]

There is no way to change the data if the row has already been copied to the "Jobs Won" sheet. Yes, you can change the data on the "Quote Sent" sheet, but these changes will not be reflected on the "Jobs Won" sheet. If you want to make changes to any of the row that have already been copied on the “Jobs Won” sheet, you must first uncheck the row you need on the “Quote Sent” sheet, make the necessary changes and then check the box again. This is the only way you will see the changed data in the row.

 

[JJFabEngineering]

There is no way to change the data if the row has already been copied to the "Jobs Won" sheet. Yes, you can change the data on the "Quote Sent" sheet, but these changes will not be reflected on the "Jobs Won" sheet. If you want to make changes to any of the row that have already been copied on the “Jobs Won” sheet, you must first uncheck the row you need on the “Quote Sent” sheet, make the necessary changes and then check the box again. This is the only way you will see the changed data in the row.

Hi Mike,
I am now having trouble with the form throwing an error. If i complete the form to where i need it would you be happy to take a look at it ?

Thanks

 

[MikeVol]

I am now having trouble with the form throwing an error. If i complete the form to where i need it would you be happy to take a look at it ?

Attach the file.