Martin
Sorry a little bit more information
The sheets are named January< February , March etc etc etc
The dates run on D3 to AH31
The names run from A4 to A98

Do i just add a sheet called summery and then what do i need to do?? is what
youv're done a macro
Sorry for sounding so dumb just getting into this excel stuff

Thanks in advance

"Martin Fishlock" wrote:

Louis

Try this. There appears to be a little bit of problem with your first row on
4 check that and amend as needed.

I assumed the sheets were called sheet1..sheet12 change as needed.

Option Explicit

Sub makesummary()

Const cszMMM As String = "Sheet"
Const cszTotal As String = "Total"
Const iYear As Integer = 2006

Dim ws As Worksheet
Dim wsdata As Worksheet
Dim iCol As Integer
Dim iEmp As Integer
Dim iDay As Integer
Dim iMonth As Integer

On Error Resume Next

Application.DisplayAlerts = False
Worksheets(cszTotal).Delete
Application.DisplayAlerts = True

Set ws = Worksheets.Add
ws.Name = cszTotal
Worksheets(cszMMM & "1").Range("A:A").Copy _
ws.Range("A1")
ws.Range("B4") = "Total"
For iCol = 3 To 27
ws.Cells(4, iCol) = iCol - 2
Next iCol
For iEmp = 5 To 98
iCol = 3
ws.Range("B" & iEmp).Formula = _
"=COUNT(C" & iEmp & ":AC" & iEmp & ")"
For iMonth = 1 To 12
With Worksheets(cszMMM & iMonth)
For iDay = 4 To 34
If .Cells(iEmp, iDay) = "H" Then
ws.Cells(iEmp, iCol) = _
DateSerial(iYear, iMonth, iDay - 3)
ws.Cells(iEmp, iCol).NumberFormat = "dd-mmm"
iCol = iCol + 1
End If
Next iDay
End With
Next iMonth
Next iEmp
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"louiscourtney" wrote:

I'm trying to work out a formula to extract dates from a spread sheet and
show them on a summery sheet.

Ive set up a sheet that has 12 tabs one for each month, in each sheet there
are names down the side (A4 to A98) and the dates along the top (D4 to AH4).
I then enter a "H" in the appropiate cell when they take holidays.
What i would like if possible is to have a formula or macro that colates all
the dates when holidays are taken and displays in the summery sheet similar
to below

Holidays Taken
1 2 3 4 5 6 7
J Bloggs 02-Jan 05-May 06-Jul 08-Aug 09-Sep
Can anyone help please