Excel VB code from Mr Excel


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

VB code
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

niijerry
Welcome to the Board


Joined: 28 Mar 2003
Posts: 4


Status: Offline

 Reply with quote  

VB code

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

icon_help.gif
_________________
Jerry T

Post Mon Apr 19, 2004 11:53 am 
 View user's profile Send private message

erik.van.geit
Board Master


Joined: 15 Mar 2004
Posts: 816
Location: Testelt
Flag: Belgium

Status: Offline

 Reply with quote  

Re: VB code

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
_________________
I love Jesus

the more we learn the more we know that there is still a lot to learn
(if this is good english)

Post Tue Apr 20, 2004 1:02 am 
 View user's profile Send private message Send e-mail

pennysaver
Board Master


Joined: 15 May 2003
Posts: 2765
Location: San Diego, California
Flag: Usa

Status: Offline

 Reply with quote  

Re: VB code

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

Post Tue Apr 20, 2004 4:15 am 
 View user's profile Send private message Visit poster's website MSN Messenger

firefytr
Board Master
Board Master


Joined: 09 Dec 2003
Posts: 2283
Location: Boardman, OR
Flag: Usa

Status: Offline

 Reply with quote  

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?
_________________
Regards,
Zack

vbax!

Post Tue Apr 20, 2004 6:11 am 
 View user's profile Send private message Yahoo Messenger

Travis
Board Master


Joined: 27 Feb 2002
Posts: 684
Location: Phoenix, Arizona
Flag: Usa

Status: Offline

 Reply with quote  

Re: VB code

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

Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
1
Data
2
ProjectLeadAssist1Assist2Cord
3
1JoeFredSallyFrank
4
2JoeSallyFredFrank
5
3FredJoeSallyFrank
6
4FrankFredSallyJoe
7
5SallyJoeFredFrank
8
     
9
     
10
Results on a seprate tab  
11
     
12
Joe1, 2, 3, 5   
13
Fred1, 3, 4   
14
Frank4   
15
Sally2, 5   
Sheet1 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Post Tue Apr 20, 2004 6:46 am 
 View user's profile Send private message Send e-mail

niijerry
Welcome to the Board


Joined: 28 Mar 2003
Posts: 4


Status: Offline

 Reply with quote  

Re: VB code

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
icon_biggrin.gif
_________________
Jerry T

Post Tue Apr 20, 2004 8:35 am 
 View user's profile Send private message

Travis
Board Master


Joined: 27 Feb 2002
Posts: 684
Location: Phoenix, Arizona
Flag: Usa

Status: Offline

 Reply with quote  

Re: VB code

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

Post Tue Apr 20, 2004 2:50 pm 
 View user's profile Send private message Send e-mail

erik.van.geit
Board Master


Joined: 15 Mar 2004
Posts: 816
Location: Testelt
Flag: Belgium

Status: Offline

 Reply with quote  

Re: VB code

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

code:
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 ?
code:
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
_________________
I love Jesus

the more we learn the more we know that there is still a lot to learn
(if this is good english)

Post Tue Apr 20, 2004 11:09 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.