Create Excel Report Sheet with VB Button

[JimmiOO]

Hi could someone please help me with this, I have attached the worksheet.

As you can see its just a Job logging spreadsheet, What I am trying to do is create a jobs out standing log that will probably go in place of the Search Results sheet.

I have a job Info sheet that is just for new jobs and a Jobs Done sheet that is for jobs done, in the Search Results sheet I would like to have Jobs Outstanding, this would be done by matching the Job Number in the Job Info & Jobs Done Info sheets and giving me a report on all outstanding Jobs. I would also like to add a Jobs Outstanding Button to the Intro Sheet.

[JimmiOO]

Any help greatly appreciated

[JimmiOO]

Can anyone help with this?

[royUK]

It would be much simpler to have one sheet to record all the jobs, then add a column to indicate the status of the job - done,cancelled,in progress etc. You can thenautofilter by this column.

Also I would have the job number in Column A.

Looking at your code it could be shortened & made more efficient by not selecting cells

[JimmiOO]

Hi Roy thanks for that, any chance someone could help in sorting this out for me? i don't have a lot of knowledge with VB.

[JimmiOO]

Hi Roy

I have changed the sheets now so that the job number is in column A on both the Job Info and Jobs Done info sheets. I have aslo added a VB button onto the intro page that goes to the outstanding job sheet. I have also added an Outstanding Job sheet with the same format as the Job Info sheet.

Phew

Could anyone help me out with a code i can apply to the Outstanding Job VB button that will look at the Job Number in column A in both Job info and job done info sheets and then list all the outstanding jobs that have not yet been done?

Is this possible?

[StephenR]

Needless to say, you would be much better off if you heeded Roy's advice. It would make life a lot simpler. In any case your attachment does not reflect your updates.

[JimmiOO]

Hi Stephen i have now uploaded the updated sheet, i did take in Roys advice but would like it done by the button if possible.

[StephenR]

That's better but not quite there.

It would be much simpler to have one sheet to record all the jobs, then add a column to indicate the status of the job - done,cancelled,in progress etc. You can thenautofilter by this column.

As far as I can see you have not done this.

Also I would have the job number in Column A.

That's better.

[JimmiOO]

Hi Stephen i would prefer to have it the way it is as far as recording the jobs go, i know i could just use an auto filter to sort them. But is it possible to do it the way i want with the button?

[StephenR]

Try this:

Sub Button21_Click()

Dim r As Long, rng As Range, rngDone As Range

Sheets("OutstandingJob").Select
With Sheets("JobsDoneInfo")
    Set rngDone = .Range("A6", .Range("A6").End(xlDown))
End With
r = 6
With Sheets("JobInfo")
    For Each rng In .Range("A6", .Range("A6").End(xlDown))
        If WorksheetFunction.CountIf(rngDone, rng) = 0 Then
            Cells(r, 1).Resize(, 10).Value = rng.Resize(, 10).Value
            r = r + 1
        End If
    Next rng
End With

End Sub

[JimmiOO]

Thanks for the code Stephen, im not sure if im putting it into the wrong place. I am putting it into the Outstanding Job sheet, when i run it it works well, but when i add more jobs, or jobs done it does not automatically show up the new data unless i go in and run the code again.

I would like to click on the Outstanding jobs button and let that automatically run the code. Im probably putting the code in the wrong place, or am i?

[StephenR]

You already have the Click event macro in your module 5 so I just added my code there. Right-click on the button and Assign Macro to check the right one has been assigned. You will have to press the button to run the code though.

[JimmiOO]

Ah brillian Stephen it works a treat, thank you for bearing with me.

[StephenR]

My pleasure, glad it worked.