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.

 

[NATTS]

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:

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.

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.

I am so sorry this is all muddled up now. Bellow are my instruction to Molly when she started I have copied and pasted them from a instruction I mailed her

Hi molly
Here bellow is an explanation of how our system currently works (very haphazardly) We are looking into streamlining it in the new year so please hang in there and we will get it sorted out.

• Only on acceptance with signature of our quote along with order number open the job sheet system(xls) found on the ‘my cloud drive’ (each computer is configured slightly differently so the drive map wont be the same) the file is located in the root and not in a folder
• On the job sheet tab in the work book enter all the details (the job number is alpha numeric and works as follows
• the first 4 digits are the year
• the fifth digit is the month starting with A
• the next 5 digits are the job number that runs from 1 to 99999 in succession regardless of the year or month
• the next 2 digits are the rep code (initials)

once you have done this save the job system file as the new job number in the Natts/current jobs folder if you don’t do this the technicians can’t edit their job cards as the folder is shared with them

• Now open the tab labelled works order and enter the details from the information filled in from the job sheet book tab dont forget the job number in the top right corner block B1. Input detailed instructions into the works order as per the notes on the rear of the quotation if there are drawings needed for the jobs, they will be attached with paper clip on the quote sheet make copies of the originals and send copies with the technician
• Open the Job costing tab input all the client’s info again into this sheet Again job number top right block B1 and write down all your purchases or costs this job incurs keep the sheet open and once the job is complete email it to me or Nikita for costing if I am not in town. Please ensure that the technician’s hand in the time cards with the job! Any time cards not handed in they will not be paid for.
• All invoices and slips job costs etc. must be scanned and labelled accordingly and sent to me or Nikita with the file for costing all original slips invoices etc are to be placed in the month file to be sent to Tammy for accounting and filing
• If you should need to access a costed job the files can be found under completed job packs on the my cloud drive in the Natts folder the packs are labelled with the job number

Mumps I hope this is detailed enough
here bellow is the sample file with data in it after it has been opened

2021A00001SA.xlsx

Shared with Dropbox

www.dropbox.com

the next file is the file with only the job number in it before she edits it ( please note i have recreated the scenario due to sensitive information

JOB SHEET SYSTEM.xlsx

Shared with Dropbox

www.dropbox.com

In relation to your question about a new sheet or new work book its a new work book the way I am doing it is very long winded so I can keep each work book separate so we can go back to it later to cost or work a query out with the client. If we had to use a spreadsheet that only had the details needed to open the new work book automatically with all data populated in the works order and the costing sheet would be amazing but my limited skills did not know how to do it and I worked my way round it by doing it manually. As for the file for the macro that creates the works order and the costing sheet I don't have one because I use the original document each time and just save as. I did have go at trying to get macros to create the sheet but I thinks its all wrong.( no computer guy but i tried) I am at home now and have the file on a flash drive I will send it to you tomorrow.

I hope this has not further confused things. I am so grateful to you for sharing your time with someone you don't even know if we do manage to get this right it will have a big impact on our time. Business is tough in Africa and as a new company to purchase software that works with a multidiscipline engineering firm is only done on a custom scale and is way out of our reach right now.

Again thanks you are amazing

 

[mumps]

Do you want the job system file saved as the new job number after the data has been entered only in the JOB SHEET BOOK or after the data has been entered in all three sheets which includes WORKS ORDER and JOB COSTING SHEET?

 

[NATTS]

HI

Sorry for the lengthly delay i have been up in Africa i think lets save it after its all created in all 3 sheets

 

[mumps]

Click here to download your file. Save the file immediately after downloading. Please note that it now has an "xlsm" extension denoting it as a macro-enabled file. Complete columns A:K in the JOB SHEET BOOK sheet pressing the TAB key after each entry. The data will be copied to the other two sheets. Next manually enter any other applicable data in the other two sheets. When all the data has been entered, click the "Save File" button on the JOB COSTING SHEET sheet. Please note that it is very important that the file is saved by clicking this button and not by using the usual "Save" or "Saveas" methods. When you need to create a file with a new job number, open the JOB SHEET SYSYTEM file, enter the new data and click the button. This is the code in Module1. Change the folder path (in red) to suit your needs. The other macro is in the worksheet code module for the JOB SHEET BOOK sheet and there are two macros in the code module for ThisWorkbook.

Sub SaveFile()
    Application.ScreenUpdating = False
    Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:="C:\Test\" & Sheets("JOB COSTING SHEET").Range("B1") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
    Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = True
    Application.ScreenUpdating = False
End Sub

 

[NATTS]

Click here to download your file. Save the file immediately after downloading. Please note that it now has an "xlsm" extension denoting it as a macro-enabled file. Complete columns A:K in the JOB SHEET BOOK sheet pressing the TAB key after each entry. The data will be copied to the other two sheets. Next manually enter any other applicable data in the other two sheets. When all the data has been entered, click the "Save File" button on the JOB COSTING SHEET sheet. Please note that it is very important that the file is saved by clicking this button and not by using the usual "Save" or "Saveas" methods. When you need to create a file with a new job number, open the JOB SHEET SYSYTEM file, enter the new data and click the button. This is the code in Module1. Change the folder path (in red) to suit your needs. The other macro is in the worksheet code module for the JOB SHEET BOOK sheet and there are two macros in the code module for ThisWorkbook.

Sub SaveFile()
    Application.ScreenUpdating = False
    Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:="C:\Test\" & Sheets("JOB COSTING SHEET").Range("B1") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
    Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = True
    Application.ScreenUpdating = False
End Sub

Hi thanks so much my pc crashed and I have been trying to locate all our threads the system I have tried to use the code written but it keeps giving me an error when i click save

 

[mumps]

What is the error message and which line of code is highlighted when you click "Debug"?

 

[NATTS]

Hi

Bellow are the messages received and I have also noticed that the works order tab is not filling out as well i dont know if this is related but though you should know

1) THIS IS THE FIRST

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Path = "" Then
MsgBox "This workbook has not yet been saved." & Chr(10) & "Please click the 'Save File' button on the" & Chr(10) & _
"JOB COSTING SHEET to save it if before closing."
Cancel = True
End If
End Sub

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("JOB SHEET BOOK").UsedRange.Offset(1).ClearContents ', "JOB COSTING SHEET"))
With Sheets("WORKS ORDER")
.Range("B1:B12").ClearContents
.Range("A14", .Range("B" & .Rows.Count).End(xlUp)).ClearContents
End With
With Sheets("JOB COSTING SHEET")
.Range("B1:B11,D4:D10").ClearContents
.Range("A15", .Range("F" & .Rows.Count).End(xlUp)).ClearContents
End With
Application.ScreenUpdating = True
2) THIS IS THE SECON

Sub SaveFile()
Application.ScreenUpdating = False
Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="C:\Test\" & Sheets("JOB COSTING SHEET").Range("B1") & ".xlsx", FileFormat:=51
Application.DisplayAlerts = True
Sheets("JOB COSTING SHEET").Shapes.Range(Array("Rounded Rectangle 1")).Visible = True
Application.ScreenUpdating = False
End Sub

 

[mumps]

I'm sorry but you haven't answered my question from Post #16.
You said:

I have tried to use the code written but it keeps giving me an error when i click save

What is the error message and which line of code is highlighted when you click "Debug"?