VB code

[niijerry]

I have an excel spreadsheet monitoring work done by staff at locations across England. Each row contains a project and is staffed by four people identified with column headings Lead, assist1 assit2 and cord. A staff can be a lead in one project and an assist in another. I need help with a VB code that will be able to pull out all projects undertaken by staff A whether they are the lead or assist1 and display the result information on a black spreadsheet within the work book called analysis. Thanks for you help all VB experts

 

[erik.van.geit]

an example will make things clearer to help
display a "before - after - sheet"
use Colo's HTML Maker utility for displaying your Excel Worksheet on the board (see bottom of the page)

regards,
Erik

 

[Smitty]

Welcome to the Board!

As Eric said, posting a shot of you sheet will certainly help, but you may want to look into Pivot Table(s) as a start as well.

And what do you mean by "black sheet"? Didja mean "blank"?

How do your folks access the workbook? Does it need to be shared? (Shared workbooks can be tricky with VBA)...

Smitty

 

[Zack Barresse]

just another couple of questions:

are you wanting to copy/paste or cut/paste the info?
and will the worksheets your working with be changing? or remain the same two sheets throughout?

 

[Travis]

This stumped me this afternoon, but I think niijerry is trying to get some code that will end up with results like this.

Book1.xls
	A	B	C	D	E
1	Data
2	Project	Lead	Assist1	Assist2	Cord
3	1	Joe	Fred	Sally	Frank
4	2	Joe	Sally	Fred	Frank
5	3	Fred	Joe	Sally	Frank
6	4	Frank	Fred	Sally	Joe
7	5	Sally	Joe	Fred	Frank
8
9
10	Resultsonasepratetab
11
12	Joe	1,2,3,5
13	Fred	1,3,4
14	Frank	4
15	Sally	2,5
Sheet1

 

[niijerry]

The spreadsheet contains approx. 1500 projects. Each staff member will be working btwn 5- 10 projects at a time. They can either be a lead, assist1 or cord on any of the projects. The sheet is used to monitor their workload and project info changes every month with details of time spent on each project. Each staff should be able to pull out their projects to monitor each project stage. So the idea was to write a VB code to display ony the info required by ecah staff. THis should not only display the project no. as suggested by Travis but every detail contained in that project.

Thanks for all your help

 

[Travis]

What about using filters? This is a standard feature found on the Data menu.

 

[erik.van.geit]

Try something like this
based on Travis' example for staffnames input

Option Explicit

Sub list_projects()
Dim i As Long
Dim max As Long
Dim staff As String
Dim sshnr As Integer 'source sheet#
Dim tshnr As Integer 'target sheet#
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
staff = InputBox("please fill in your name", "NAME", "")
max = Range("A65536").End(xlUp).Row
'should be a form with all names
'isn't casesensitive: users may type without uppercase
sshnr = ActiveSheet.Index
Sheets.Add after:=Worksheets(Worksheets.Count)
tshnr = ActiveSheet.Index
Sheets(tshnr).Name = staff & " " & Format(Month(Date), "mmmm")

For i = 1 To max
    If Application.WorksheetFunction.CountIf(Sheets(sshnr).Range("B" & i & ":E" & i), staff) > 0 Then
    Sheets(sshnr).Range("B" & i & ":Z" & i).Copy 'range has to be adapted
    Sheets(tshnr).Activate
    Range("A65536").End(xlUp).Offset(1, 0).Activate
    ActiveSheet.Paste
  End If
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I wonder which way there is to avoid jumping from one worksheet to another
why doesn't this work ?

Sheets(sshnr).Range("B" & i & ":Z" & i).Copy Destination:=Sheets(tshnr).Range("A65536").End(xlUp).Offset(1, 0)

(this should be quicker if it worked)

There is some errortrapping to do.

regards,
Erik