This code assumes the data is in the 1st sheet of the workbook you are
opening and the data will be put in the 1st tab of the workbook where the
macro is located

you can change the following two lines as required

from
Set DataSht = DataBk.Sheets(1)
Set SumSheet = ThisWorkbook.Sheets(1)
to
Set DataSht = DataBk.Sheets("Sheet2")
Set SumSheet = ThisWorkbook.Sheets("Sheet3")


Sub GetResources()

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox ("Cannot open file - exiting Macro")
Exit Sub
End If

Set DataBk = Workbooks.Open(Filename:=fileToOpen)
Set DataSht = DataBk.Sheets(1)

Set SumSheet = ThisWorkbook.Sheets(1)

SumRow = 1
With DataSht
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Range("B" & Rows.Count).End(xlUp).Row

For RowCount = 2 To LastRow
RowType = UCase(.Range("B" & RowCount))
SumString = ""
If RowType = "BUDGET" Then
Task = .Range("A" & RowCount)
SumSheet.Range("A" & SumRow) = Task
SumCol = 2
For ColCount = 3 To LastCol
Hours = .Cells(RowCount, ColCount)
If Hours 0 Then
Resource = .Cells(1, ColCount)
SumSheet.Cells(SumRow, SumCol) = Resource
SumCol = SumCol + 1
End If
Next ColCount
SumRow = SumRow + 1
End If
Next RowCount
DataBk.Close savechanges:=False
End With
End Sub


"Jim@Tech" wrote:

I wonder if someone could suggest some cunning macro code that will make my
life a lot easier...

I have a list of tasks in column A, each task has three rows for budge,
actual & variance, and the columns to the right list all the resources
assigned to the project. Where a resource is sheduled to work on a task, the
number of hours they are budgeted is entered against that task for that
resource, as below:

Resource 1 Resource 2 Resource 3
Task 1 Budget 5 0 7
Actual
Variance
Task 2 Budget 0 0 2
Actual
Variance
Task 3 Budget 2 7 0
Actual
Variance

There are 98 tasks and 161 resources at the moment, so quite a lot of data.
What I would like to be able to do is summarise the resources against each
task in a seperate workbook. It's easy enough to set up an If statement that
copies the resource name if the Budget entry is greater than 0 for a given
task, i.e:
If(c20,C1,"") and copy that along the row of resources to get this:

Task 1 Resource 1 Resource 3
Task 2 Resource 3
Task 3 Resource 1 Resource 2

but this is still pretty clunky.

What I'm hoping for is a bit of code that will give me something more like
this:
Task 1 Resource 1 Resource 3
Task 2 Resource 3
Task 3 Resource 1 Resource 2



Any ideas?

Thanks
Jim.