Calculate data to not coping duplicates to another worksheet

[Divinedar]

I have several worksheets that list the following data on each sheet but different project number:

A B C D E F G
Date Hours Project Description Task/Account Comments Employee Hours Total
Aug-19 4.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 Mexico iDirect; James 34.00
Aug-20 4.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 Staff Meeting, James
Aug-21 8.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 MTTS & TCT Drawings James
Aug-22 8.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 MTTS/TCT Drawings James
Aug-23 8.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 MTTS/TCT Drawing James
Aug-19 2.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 Research and John

Aug-21 2.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard 13.00
Aug-22 3.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard
Aug-19 4.00 P13017-TELESAT RFI/ROM CISCEA P13017 James
Aug-20 4.00 P13017-TELESAT RFI/ROM CISCEA P13017 James

All the sheets within the workbook has the same layout as above.

What I want to do is gather information from each sheet in the following format into one worksheet

Task/Account Total Hours
P09998 34
P13017 13
etc...

I need the code to go through each sheet and if that Task/Account already exist within the main sheet (calculating sheet) then just add the hours to what is already there.

Such as:

Date Hours Project Description Task/Account Employee Hours Total
Aug-14 4.00 P09998 BID&PROPOSAL PRE-PREPOS P09998 Richard 4.00

Aug-12 8.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard 45.00
Aug-13 8.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard
Aug-14 4.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard
Aug-15 8.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard
Aug-16 8.00 P13017-TELESAT RFI/ROM CISCEA P13017 Richard
Aug-14 5.00 P13017-TELESAT RFI/ROM CISCEA P13017 James
Aug-15 4.00 P13017-TELESAT RFI/ROM CISCEA P13017 James

Task/Account Total Hours
P09998 38
P13017 58
etc...

So it would calculate all the Task/Account number individually per worksheet into the calculating sheet.. Each task/account is calculated under HOURS TOTAL in each worksheet.

Thank you and let me know if there is anything else I can add to make it understable.

[Klahr_R]

I am not sure I follow all the details. If I have this right you want to cycle through all the sheets in a work book picking out items that match items in the Calculating Sheet.
When a match is found you want to add, in the Calculating Sheet, to the Sum of hours for each employee for for each project.

That would amount to a significant amount of work.
The objective would look something like this
Declare the following

Dim SourceCell As String
Dim TargetCell As String
Dim SourceRow As Integer
Dim TargetRow As Integer
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

Set TargetSheet = Calculating Sheet
Loop through the rows of the Calculating sheet by indexing TargetRow.

TargetRow = TargetRow + 1
TargetCell = "C" & Cstr(TargetRow)

For each line in the Calculating sheet you will loop through one or all of the worksheets, depending on how you want to set it up.
A simple way to loop through the worksheets is like this

For each SourceSheet in Worksheets
    'code to find project number match
    'Add matching data to Summary Sheet
Next TargetSheet

You index through the rows of the Source Sheets the same as the target sheet.

You check for matches

SourceSheet.Range(SourceCell).Value = _
    TargetSheet.Range(TargetCell).value

When a match is found you can use .Offset to add the hours to the project/employee's hours total
The Employee is Plus 4 Columns
The line hours is -1 column from the project column:

Select Case  SourceSheet.Range(SourceCell).Offset(0,4).Value
    Case = "James"
        'Add the line total to the Calculating Total
        TargetSheet.Range(TargetCell).Offset(0,-1).value = _
            TargetSheet.Range(TargetCell).Offset(0,-1) + _
            Sourcesheet.Range(SourceCell).Offset(0,-1)
    Case = "Richard"
        'Don't know how you have this arranged but the Offset(Row, Column)
        'Is a way to access the required cell with out changing the search cell
    Case = "etc."
End Select

That is a rough idea, but it should get you started.

[Divinedar]

OK you just about got it right but I want to do he project number and not the employee. If the code cycles through the sheets sometime the project number on several sheets. I want just want the project number and the sum of the number of hours.

If it goes to the next sheet and that same project number exist just add the hours i.e.;

Worksheet 1 (This is just the information I need out of each worksheet)
Task/Account Total Hours

 P12065     38
 P13017     58

Worksheet 2 (This is just the information I need out of each worksheet)
Task/Account Total Hours

P12065    38
 P13017     58
 P12035     10

Calculate Worksheet
Task/Account Total Hours

 P09998     53
 P13017     75
 P12035     10

So it also adds any project number that is not on the list.

[Divinedar]

It could be many project numbers so doing a case statement won't work, to many.

[Klahr_R]

That is a bit easier, then.
The Select Case was only there to distinguish the employee so it wouldn't be needed for what you want.

So, in your first example, the total hours for P09998 is 34 hours and is on the end of the first line of that project. (Column H?)

For that, as you loop throught the worksheets you will be checking for values of column C. When found you check for a value in
Sourcesheet.Range(SourceCell).Offset(0,5)
(I believe that gets you to the "Total" column.)
If you have a value in that column then add it to the total on the total on the Calculate sheet.

You will need to know when to stop searching the work sheets. That can be found for each sheet with the following

Dim X As Long
    X = SourceSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    For SourceRow = 2 to X
        'search for match on Account number
        'Add found values to Calculate Total
    Next SourceRow

I hope that is closer to the mark.
Regards

[Klahr_R]

This wasn't as bad as it first looked. I set up 4 sheets with the first being named "Calculate"
The following code summed up the sample data that you gave above that was entered in each of the three other sheets:

Private Sub SumHours()
Dim TargetSheet As Worksheet
Dim SourceSheet As Worksheet
Dim TargetCell As String
Dim TargetRow As Integer
Dim SourceCell As String
Dim SourceRow As Integer
Dim FindValue As String
Dim LastRow As Long

    Set TargetSheet = Sheet1
    TargetRow = 3
    TargetCell = "A" & CStr(TargetRow)
    Do While Sheet1.Range(TargetCell).Value <> ""

        'Use FindValue for search criteria
        FindValue = Sheet1.Range(TargetCell)

        'Loop through the worksheets
        For Each SourceSheet In Sheets
            'Skip Sheet1 Which has been named "Calculate"
            If SourceSheet.Name <> "Calculate" Then
                'Set the Source Cell to the first data line
                LastRow = SourceSheet.Range("A65000").End(xlUp).Row
                For SourceRow = 3 To LastRow
                    SourceCell = "C" & CStr(SourceRow)
                    With SourceSheet.Range(SourceCell)
                        If .Value = FindValue Then
                            If IsNumeric(.Offset(0, 5).Value) Then
                                TargetSheet.Range(TargetCell).Offset(0, 1) = _
                                TargetSheet.Range(TargetCell).Offset(0, 1) + _
                                .Offset(0, 5).Value
                            End If ' offset
                        End If
                    End With ' source cell = findvalue
                Next SourceRow
            End If 'source sheet name
        Next SourceSheet

        'Index to next target cell
        TargetRow = TargetRow + 1
        TargetCell = "A" & CStr(TargetRow)
    Loop ' targetcell <> ""
    Set TargetSheet = Nothing
    Set SourceSheet = Nothing
End Sub

This code was placed in a code module. If you put the cursor in the code then click run you should get what you need.

[Divinedar]

Ok thanks for the code but I'm confused between the target sheet and source sheet. It doesn't get pass this part of the code:

     Set TargetSheet = Sheet80
    TargetRow = 3
    TargetCell = "A" & CStr(TargetRow)
    Do While Sheet80.Range(TargetCell).Value <> ""

Let me see if I understand this right. The TargetSheet is the sheet where the all the information collected will go and the SourceSheet are all the worksheets within the workbook?

I've attached 2 sheets in a pdf one is a targetsheet and a sourcesheet to give you an idea of what I'm working with. All the sourcesheets are in the same format.

[Klahr_R]

The "TargetSheet" becomes an alias for the sheet where you want to accumulate totals. (You get data from the source and put it in the target.) Once set it will be referenced as TargetSheet, not Sheet80.

The PDF appears to be two Source sheets.

If you have blank rows in your Target (the one with the list of account numbers to be totaled) then the code will stop on that blank line. If you need the blank lines the code will have to be changed to code similar to what is done with the rows on the SourceSheet.

Now for matching columns and rows in the code with columns and rows in you workbook.
There are 5 values for rows, columns and offsets that have to match between the code and you actual worksheets.
The first three I can give you because of your sample in the PDF.
Per your PDF your Source Cell (line 25 and 26 of the code I posted above) should be set as

 For SourceRow = 2 To LastRow
    SourceCell = "D" & CStr(SourceRow)

and the offset (line 32) should be 3 instead of 5

Matching Rows and Columns on the Calculate (TargetSheet)

Change the number 3 (in line 12) to the value of the first row where an account number will be found.
Also the column letter to match the column where the account numbers are listed. Make that change on line 13 and again on line 42.

Let me know how that works

[Divinedar]

ok so I had to change

Do While Sheet1.Range(TargetCell).Value <> ""

to

Do While Sheet80.Range(TargetCell).Value = ""

for it to go to the next line.

But when I get here

FindValue = SourceSheet.Range(SourceCell)

I get a "runtime error 91 object variable or With block variable not set"