• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking pages & numbering

J

John Thompson

New Member
Hi All,

I am very much a novice at excel so thankyou in advance.

I have been reading through this forum & trying to make a macro for the attached spreadsheet with no success.

I have manually created the 25 work order sheets while I have been trying to create a macro, what I am trying to acheive is a command button which will create a new work order sheet from the template and also number the sheet, place the work order number into "B2", and link the 'date received", "date completed" & "sub total cost" back to the master sheet, in each sheet and also create a hyperlink on the master sheet from the job no.

Again Thankyou

John
 

Attachments

  • Master Job Sheet.xlsx
    133 KB · Views: 10
Hi John,

I have attached a sheet. I hope, this is as per your expectation.
Here I have some assumption that Sheet Name and Job No. will always start from 1001, 1002, 1003 and so on.
 

Attachments

  • Master Job Sheet.xlsm
    54.3 KB · Views: 15
Hi John ,

The Template sheet has been hidden so that no one can tamper with it. If you wish to make it visible , there are two ways you can do this :

1. In the Immediate window of the Visual Basic editor , type in the command :

Sheet1.Visible = xlSheetVisible

When you are done with modifying it , you can issue the statement :

Sheet1.Visible = xlSheetVeryHidden

2. In the VBE , in the Project Explorer pane , place the cursor on Sheet1 ( Template ) , and press the F4 key to bring up the Properties window ; go down to the Visible property , and in the right side drop-down , select -1 - xlSheetVisible. When you are done , you can again change its Visible property to 2 - xlSheetVeryHidden.

Narayan
 
Hi,

We have been using the above spreadsheet created by NARAYANK991 (very much appreciated) we have a process where on the master job sheet if a job is still active it is left white, completed yellow & then once invoiced red, is there a way of simply hiding all jobs filled red but they would still be easily accesible if required. I have tried filtering with no success.

Thanks in advance

John
 
NARAYANK991 said:
Hi John ,

Another solution.

Narayan
Click to expand...
Hi Narayan,

You constructed the attached workbook for me a couple of years ago, it has worked great and is much appreciated, I was wondering if you would mind helping me again as I'm not sure and don't want to upset the codes, if I create a new work order, as I populate the information in the actual work order is it possible for that information to populate the corresponding items in the reports worksheet?

Thanks in advance, I would buy you a drink if I could.

Cheers

John
 

Attachments

  • Master Workorders 2016:2017.xlsm
    256.7 KB · Views: 12
Hi John ,

I have not understood your requirement ; can you explain with an example ?

Which is the tab you refer to as the reports worksheet ?

Narayan
 
NARAYANK991 said:
Hi John ,

I have not understood your requirement ; can you explain with an example ?

Which is the tab you refer to as the reports worksheet ?

Narayan
Click to expand...

Hi Narayan,

Thanks for your reply,

In the master tab, if you click "New Worksheet" this will create a new work order, this will be "1002" when i fill in WO Location/No:, CRM or PO No: Received Date & Completion Date, it would be great if this information could go straight into the reports tab which is located next to the master tab.

As per the attached, so each time we fill in a new work order the entered data will go into the master sheet and the reports sheet.

Hope this makes sense

Thanks

John
 
Last edited:
Hi John ,

In the workbook you have uploaded , other than the tab named Master , all the other tabs are work order tabs , such as 1000 , 1001 , 1002 , all the way till 1026.

There is no tab named reports.

Narayan
 
Hi John ,

Sorry , my mistake ; I must have downloaded the file from my own post.

I have now downloaded the file from your post , and I am finding a problem.

When I click on New Worksheet , the new tab that is created , has the following :

WO / Location No. ---- cells B2:L2 ( merged cells )

CRM or P/O No. ------- cells B4:C4

Received Date -------- F4

Completion Date ------ I4

I am unable to understand what is meant by Status ; from which cell will this be transferred ?

What is strange is that in the two tabs 1000 and 1001 which were already present in the workbook , these references are different ; why ?

Narayan
 
Sorry Narayan,

1000 & 1001, were created from the original before I tinkered with the template, 1002 & 1003 are as we would like them for this financial year and are correct, I didn't want to delete them and effect any codes/formulas, sorry I should have also mentioned status is simply where i can place notes, nothing has to correspond with status.

Thanks again
 
Hi Narayan,

Is it possible to have it similar to the attached, so when we fill out each work order it automatically populates the report sheet, similar to the master sheet, we are attempting to create the same principle as the master sheet, just with no dollar figures basically.

Cheers

John
 

Attachments

  • Master Workorders 2016-2017 (4).xlsm
    273.9 KB · Views: 2
Hi John ,

I would like to avoid data transfer as it is being entered in the work order tabs , basically for two reasons :

1. There are multiple items to be transferred from the work order tab to the reports tab ; the first item which is to be transferred will have to be transferred on to a new row in the reports tab , but thereafter , the remaining items will have to be transferred in the same row , in the appropriate cells. Doing this is more coding.

2. More important is if a mistake has been made in data entry ; any correction will have to be made in the appropriate entry in the reports tab. Doing this is more coding.

If you really want it done , I will do it , but it will take some time.

Narayan
 
Hi Narayan,

Thanks again for your prompt reply, the transfer data button you have created seems to replace all information in the same row on the report tab, replacing the previous work order, if it is not to much trouble are we able to have each work order running on the report tab, this is the report we would like to forward onto others to indicate the status of each work order, happy to reimburse you for your time if I can.

Thanks
 
Hi John ,

Sorry , but the transfer of data on to the same row in the reports tab is a mistake ; if that is the problem , I can certainly correct it.

Will that do ?

Narayan
 
Cheers Narayan,

Sensational, saves us a lot of time at our end, thanks again, how do I reward you for your time & knowledge & effort?

John
 
You must log in or register to reply here.
Back
Top