Dynamic sorting and selection of data

[Guest]

Hi All,

I am not very conversant with excel VBA programming but I have a certain
activity to complete which requires me to do that.Currently, I am stuck right
at step one.

I am trying to create a very basic resource/project planning worksheet which
will be dynamically picking up data from another worksheet in the same
workbook. Looks pretty simple but the idea is that there should be some
behind the scene calculations and selections.

To explain:
I have a worksheet which has a listing of team members and what projects
they have been allocated to, and for how much of their time per week (i.e.
40% or 60% of a normal 40hrs workweek). This information can change over a
period of time.

Now I have another worksheet which ideally when opened should list out each
team member, show the project(s) he/she is assigned to, and show the hours
(not % , which means the program picks the allocated % and calculates the
hours and displays that) per week that he/she has to spend on that project.
This should be continuing till the last of the team member has been listed
out.

If anyone can point me in the right direction or provide links to resources
for (hopeless) beginners, it would be a great help.

Thanks!!

 

[Guest]

I tlooks from your description the 2 worksheets are identical except one
displays % and the other displays hours? Then to convert from % to hours you
need to multiply by 40?

You may not need VBA just a simple formula.

if on sh1 cell B7 has 40%, then in sheet2 place the following formula

=40 * sheet1!B7

This formula can be copied to every cell on sheet2 where hours are required.
If you want to skip the cell where 0 is placed then add an if statement

=if(sheet1!B7 <> 0,40 * sheet1!B7,"")


You would only need VBA code if the two sheets were arrange differently. I
can't tell how different the two sheets are arranged from the information
provided. when you said the worksheets are dynamically changing. Does that
mean new projects and new team members are beeing added or just the % and
hours changing.

 

[Guest]

Hi Joel,

Thanks for the reply. It seems that you dont understand what I am trying to
do.
The following is the structure of the excel sheet which I have in my
workbook. Here as and when required a new project can be added and resources
allocated to them. This is more like the place where the manager fills
information:

Request Status Resource - 1 Work % Resource - 2 Work %
Project 1 R1 35
Project 2
Project 3
Project 4 R1 20
Project 5
Project 6
Project 7 R2 32
Project 8

In the same work book I have another sheet where I would like to query this
worksheet and display information like below

Resource Project Time Allocated
R1 Project 1 14hr (35% of 40)
R1 Project 2 08 hrs (20% of 40)
R2 Project 7 12.8 hrs (32% of 40)

I want to achieve this by using VBA and not doing it the conventional way of
auto-selecting, copy, paste, and then some calculations. That is, I want it
to be more dynamic in nature rather than manual.

Thanks!!

 

[Guest]

Try this code. Let me know how it works. Modifiy the two Const to define
your source and destination worksheet. I first put the data in the
destination sheet and then run a sort on the sheet.

Sub getresouses()

Const SourceSheet = "Sheet1"
Const DestSheet = "Sheet2"

With Worksheets(DestSheet)
LastrowDest = .Cells(Rows.Count, "A").End(xlUp).Row
End With
NewRowDest = LastrowDest + 1


With Worksheets(SourceSheet)
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column


For RowCount = 2 To Lastrow
For Colcount = 2 To LastCol Step 2
If Not IsEmpty(.Cells(RowCount, Colcount).Value) Then
Resource = .Cells(RowCount, Colcount).Value
PercentHours = .Cells(RowCount, Colcount). _
Offset(0, 1).Value
Project = .Cells(RowCount, "A").Value
hours = 0.4 * PercentHours
HourString = hours & " hrs (" & PercentHours & "% of 40)"
With Worksheets(DestSheet)
.Cells(NewRowDest, "A").Value = Resource
.Cells(NewRowDest, "B").Value = Project
.Cells(NewRowDest, "C").Value = HourString
End With
NewRowDest = NewRowDest + 1
End If

Next Colcount
Next RowCount
End With
With Worksheets(DestSheet)
Range("A2:C" & (NewRowDest - 1)).Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess
End With
End Sub

 

[Guest]

I left off a period on one statement
from
Range("A2:C" & (NewRowDest - 1)).Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess
to
.Range("A2:C" & (NewRowDest - 1)).Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess

 

[Guest]

There need to be 3 periods in this statement
from:
Range("A2:C" & (NewRowDest - 1)).Sort _
Key1:=Range("A2"), _
Order1:=xlAscending, _
Key2:=Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess
to:
.Range("A2:C" & (NewRowDest - 1)).Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Key2:=.Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess