CREATING A SHEET AUTOMATICALY FROM DATA ON THE MASTER SHEET

[NATTS]

HI ALL

I am a small business owner in Africa and wanting excel to create new job sheets for me when my office lady inputs new data into it. it has a set set of parrameters in the table and when we open a new job by entering the new data i want it to open a new sheet so that we can enter all the purchases etc.

I am not a computer fundi and africa is quite far behind in the learning processes when it comes to It

and advice on how to do this.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Better still, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[NATTS]

THANK YOU

In short my master sheet is what I call my Job book that allow my lady to enter a new job received this acts as a quick reference for us to see when a job was done etc. Once this line of data is input i need it to take that data and put it onto our works order and allow that works order to be sent to the technician who then fills it out on site on line via his phone or tablet. This way the duplication of work is minimized in addition to this it will open a job costing sheet that she can work on to carry out purchases so that the cost of the work and parts doesn't go over quoted values.

I cannot get my system to download the required add on and i can see to get a share link can i mail it to you

 

[mumps]

If you can upload the file to www.box.com or www.dropbox.com, you can simply post the link to your file here.

 

[NATTS]

JOB SHEET SYSTEM.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

There are a few misspelled headers in the JOB SHEET BOOK sheet. Please correct the headers in J1 and K1 and remove the trailing space in J1. Also, in the WORKS ORDER sheet, "ALLOCATED TECHNICAN " has a trailing space. Please remove that extra space as well. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your JOB SHEET BOOK sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the data in the JOB SHEET BOOK sheet and press the RETURN key or TAB key after each entry.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B:K")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, fnd As Range
    Set desWS = Sheets("WORKS ORDER")
    Set fnd = desWS.Range("A:A").Find(Cells(1, Target.Column).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        If fnd.Row <> 13 Then
            fnd.Offset(, 1) = Target
        Else
            With desWS
                lrow = .Range("A13", .Range("A" & .Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
                .Range("A" & lrow) = Target
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[NATTS]

There are a few misspelled headers in the JOB SHEET BOOK sheet. Please correct the headers in J1 and K1 and remove the trailing space in J1. Also, in the WORKS ORDER sheet, "ALLOCATED TECHNICAN " has a trailing space. Please remove that extra space as well. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your JOB SHEET BOOK sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the data in the JOB SHEET BOOK sheet and press the RETURN key or TAB key after each entry.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B:K")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, fnd As Range
    Set desWS = Sheets("WORKS ORDER")
    Set fnd = desWS.Range("A:A").Find(Cells(1, Target.Column).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        If fnd.Row <> 13 Then
            fnd.Offset(, 1) = Target
        Else
            With desWS
                lrow = .Range("A13", .Range("A" & .Rows.Count).End(xlUp)).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
                .Range("A" & lrow) = Target
            End With
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Thank you so much!!!!!!!!
Sorry for the late rely I have been away from my desk a few days. Without sounding ungrateful as I assure you I am really not, I have noticed the info does not carry across to the job costing sheet could that be included as well. I would also like the job number to populated in the blank block (1B) of both the sheets and this to be used as the file number for saving the documents
Is there also a way for it to open a new sheet each time a new line is used in the job sheet book and use that info that is populated from the new entry. And get it to automatically save the new sheets in a predetermined place with the job number so if our lady forgets to do so there is folder somewhere on the computer that it can be pulled up. If I need to give this to you I can do so


Thanks again to you for the awesome help your file is so short and what I started writing via the record function now 5 a5 pages long already and I still have to do the job costing sheet

Regards

Shaun

 

[mumps]

Is there also a way for it to open a new sheet each time a new line is used in the job sheet book

Do you mean "open a new workbook"? Do you want to save the files in the same folder as the JOB SHEET SYSTEM workbook? If not, what is the full path to the folder where you want to save the files? Also, you understand that the newly saved files will not contain the TOTAL HOURS WORKED in WORKS ORDER and there will be no data starting in row 15 of JOB COSTING SHEET.

 

[NATTS]

Maybe I should rather explain what i am trying to achieve

1) Generate new job sheet for lady to work with in the office and generate new job card for the fitters to use in the shop or onsite
2) The job sheet book is where the new job is captured and when the order is received and this is used as quick reference to generate the alpha numeric job number and that in turn becomes our product serial number
3)The job number needs to be on the top right of the job sheet and the job card and these new sheets need to be saved in separate folders called job packs that we currently create manually when the job starts

Notes

-We have had an instance or 2 where the lady in the office is overwhelmed and forgets to create the job pack file and and we have to search the entire system for all the bits and pieces
-the data that we put in to the job packs is copies of the invoices, receipts, clock cards, job photos, safety files etc. etc...
- Basically i am trying to automate a manual system that we currently use which is replicated 4 times and Molly in the front office wont be so overwhelmed and i wont need to spend as much time in the office costing etc.

hope this explains things better

 

[mumps]

I'm sorry but I don't follow. Please understand that what may be very obvious to you because you deal with the problem every day, it is not obvious to me. When you say:

Generate new job sheet for lady

I need to know what that new job sheet looks like. Also, I'm not sure if you mean new "sheet" or new "workbook". I need the distinction between "sheet' and "workbook" clarified. If a new new "sheet" or new "workbook" has to be created, I need a copy of the file in which the macro that does the creation is located and I need a copy of what the new "sheet" or new "workbook" looks like. How is the alpha numeric job number generated? Do you enter it manually? You also mention "a job card file" which wasn't mentioned in your original post. I have no idea what a job card is or what it should look like.

these new sheets need to be saved in separate folders

How do you determine which new "sheet" or new "workbook" is saved to which folder? Do you save them manually?

In order to begin finding a possible solution, I need a very clear idea, step-by-step, of what the lady is starting with and what the end result should look like. So please upload a copy of the file that Molly starts out with including some sample data in all the columns and a copy of the file, manually created, that shows the end result based on the the sample data in the starting file. Macros are extremely picky and unforgiving. They will give you exactly what you ask for and they want exact instructions on how to get to the end result. In order to give the macro what it demands, I need to know exactly what to give it. I hope you understand.