Copy entire row and paste it in another sheet

[JasonBing]

I need a code to do the following.

I have a sheet of job numbers (called FTBJOB2). The job numbers are located in column b of each row. There is then data pertaining to that job in the remainder of the row. For example Row 13 has job number 123 in column b Then in column c through about ak is the data for that job. There is a new sheet created for each job using the job number as the sheet name. I need a macro to do the following, after the cell containing the job number is selected on the active sheet (called FTBJOB2)

1. search the workbook for a sheet call "that job" number

3. if not found then message box "Job card (number in selected cell) doesn't exist" end sub

4. if sheet with job number from selection is found, then copy entire row of that selected cell in FTBJOB2 and paste it in row 56 of the sheet with that name (from column 2)

5. Message box "job card updated successfully

6. return to FTBJOB2 A1

I hope this makes sense.

Thank to all you wonderful people and all your help

Regards

Jason Bing

 

[gallen]

Hello, this code goes in to your FTBJOB2 worksheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'information was incomplete in OP. You state to copy row and paste to 2nd column of row 56. _
This can only be done with copied range, not a copied row.


Dim ws As Worksheet
Dim bFound As Boolean
Dim lc As Long 'last used column


On Error GoTo errHandle


    If Target.Column = 2 Then 'user has changed column B
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = Target Then
                Application.EnableEvents = False
                bFound = True
                lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
                Range(Target, Cells(Target.Row, lc)).Copy
                ws.Cells(56, 2).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                Application.EnableEvents = True
                Exit For
            End If
        Next ws
        If bFound = False Then
            MsgBox "A sheet named '" & Target & "' could not be found.", vbInformation, "Not found"
        End If
        Application.Goto Range("A1")
    End If
Exit Sub
errHandle:
    MsgBox Err.Description, vbCritical, "Error No. " & Err.Number
    Application.EnableEvents = True
End Sub

 

[JasonBing]

I see your point.

Let me clarify with more detail. It was late last night when I posted this! There is a better question for what I am trying to achieve.

There are two parts to the job management.

1. The Customer order Sheet (FTBJOB2). On this page, the guys use a VBA data entry form to populate a data table. There is a lot of information from the customer so the data form fills in from column B (= Job Number) all the way to column AL. When they press the add order to register button on the data entry form it puts the data into the next available row. The result, is a table populated from column b to AL with the job information and each job in a different row.

2. The job cards. As mentioned we create a new sheet for each job. These are named using the job number (found in column B of the Customer order sheet) When it is time to put the job into production, we go to the Customer order sheet, select the relevant job number from column B of the jobs row and prep the create job card button.

The macro in this button does the following.

2a Creates a new sheet from another template sheet and gives it the name contained in the selected cell, from the customer order sheet (ie column B or job number)
2b copies the data contained in the row of the selected cell in customer order sheet to row 56 of the newly created sheet.
2c The new job card sheet takes the data from row 56 and populates the relevant cells in that sheet.

We are then left with a customer order sheet (with job number in column B) and a job card sheet named with that job number.

This all works great.

The problem is that the customer often changes the order!!!

For this there is update customer order vba form on the customer order sheet (FTBJOB2)

When the the guys call this form they select the job number from a drop down box on the data entry form, all the data from that job (contained in columns C - AL) populates the form. Then they can then edit each text box as needed. When the press the "update customer order" button, it updates the data in the table as needed.

What I need is a code to add to the "update customer order" button, that will update that row 56 on the sheet named with the same job number.

Your code worked beautiful by the way. However the guys need to be able to select the cell in column B to create the job card sheet

I hope this is more clear.

I really appreciate your help on this and I hope this is a better question

Regards

Jason Bing