Excel Worksheet Functions

[Newfie809]

I have a workbook that has 44 worksheets with a list of Employee Names, FTE,
Job Title and their Location.

I have to list all information on one worksheet at the end, so I can attach
their salaries and allowances for budget.

All worksheets are in the same format.

Location for all employees is D3 in all worksheet. and Emplyee names are
listed in rows B9 to B48, S4 to S26 and S29 to S52.Their FTE's are listed in
rows C9 to C48, U4 to U26 and U29 to U52. Job Title for employees listed in
rows B9 to B48 is in row B8. Job Title for employess rows S4 To S26, S29 to
S52 is listed in Rows R4 to R26 and R29 to R52.

Each worksheet might not have employees in all rows, some might have up to
40 employees and some as little as 8.

I have listed all information for each sheet in columns AA1 Name, AB1 FTE,
AC1 Job Title and AD1 Location, then on the last worksheet I did = for each
cell that containe data.

I was hoping that there might be an easier way.

Thanks

 

[Otto Moehrbach]

Newfie

The "easier way" is VBA (programming). I'll try to work up
something for you but I need clarification on some of what you said.

You say:

Job Title for employees listed in

rows B9 to B48 is in row B8.

B8 is not a row. It is a cell. Do you mean that the job title for all
employees in B9:B48 is the same and that title is in cell B8?



You say that you want the Name, FTE, Job Title, and Location for each
employee to be placed in the last sheet. Is this "last sheet" one of the 44
sheets that already have employee data, or is this "last sheet" dedicated to
just the compilation of all 44 sheets and is therefore the 45th sheet? Otto

 

[Newfie809]

Yes, that is correct Job Title for those employees is all the same and is in
Cell B8.

 

[Newfie809]

Yes there are 44 locations and the last sheet is number 45 (All Employees).

 

[Newfie809]

I was reading what I had written and I forgot to say that an employee can be
listed in more that one cell and more than one location. They could be a .50
do one job and a .50 do the same job but at a different location. Do you
think you can still help?

 

[Otto Moehrbach]

Newfie

Here is some code that will do what you want. The name of the
45th sheet is in the code as "Last Sht". Change this in the code as you
wish. The listing of employees, etc, starts in Column AA as you said.

The code will get the necessary data from EVERY sheet that is
NOT named "Last Sht". The code will not extract data from the "Last Sht"
sheet, only paste data gotten from the other sheets in the file. If you
need to exclude any other sheets, let me know. Note that the order you have
the sheets in is not important.

Notice that this code consists of 4 macros and some
declarations. Paste all the code (shown below) into a regular module. The
only macro you need to run is named "SumData". The other macros are run
within the code.

Note that the number of sheets in your file and the sheet names are
immaterial. The only sheet name that is hard-coded is the "Last Sht" sheet.

If you wish, send me an email and I'll send you the file I used to develop
this code. This file will have the code properly placed. My email address
is (e-mail address removed). Remove the "nop" from this address.. Otto

Option Explicit
Dim Rng As Range
Dim i As Range
Dim RngName As Variant
Dim ws As Worksheet
Dim Dest As Range
Sub SumData()
Application.ScreenUpdating = False
Sheets("Last Sht").Select
Set Dest = Range("AA" & Rows.Count).End(xlUp).Offset(1)
Call NameRngs
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Last Sht" Then GoTo SkipSht
For Each RngName In Array("B9B48", "S4S26", "S29S52")
With ws
Set Rng = .Range(Range(RngName).Address)
If Application.CountA(Rng) = 0 Then GoTo SkipRngName
Call SetRng
Call AConsolidate
End With
SkipRngName:
Next RngName
SkipSht:
Next ws
Application.ScreenUpdating = True
End Sub

Sub NameRngs()
Range("B9:B48").Name = "B9B48"
Range("S4:S26").Name = "S4S26"
Range("S29:S52").Name = "S29S52"
End Sub

Sub SetRng()
With ws
Set Rng = .Range(Rng.Address)
If IsEmpty(Rng(Rng.Count).Value) Then _
Set Rng = .Range(Rng(1), Rng(Rng.Count).End(xlUp))
End With
End Sub

Sub AConsolidate()
With ws
For Each i In Rng
Dest.Value = i.Value 'Name
Dest.Offset(, 3).Value = .Range("D3").Value 'Location
If Left(RngName, 1) = "S" Then
Dest.Offset(, 1).Value = i.Offset(, 2).Value 'FTE
Dest.Offset(, 2).Value = i.Offset(, -1).Value 'Job
Title
Else
Dest.Offset(, 1).Value = i.Offset(, 1).Value 'FTE
Dest.Offset(, 2).Value = .Range("B8") 'Job Title
End If
Set Dest = Dest.Offset(1)
Next i
End With
End Sub