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

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

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

Ok, this is a macro and you copy the code below into a module in the vb
editior.
You do this by
Alt+F11 (open vb editor)
Insert Module
then paste it.

Go back to excel and
Alt+F8 to open macros and run makesummary

there is no need to insert a sheet as it does it automatically.

Have fun and please rate the reply to close it. thanks.


Option Explicit
Option Base 1


Sub makesummary()
Dim ws As Worksheet
Dim wsdata As Worksheet
Dim iCol As Integer
Dim iEmp As Integer
Dim iDay As Integer
Dim iMonth As Integer
Dim szMonths As Variant
szMonths = Array("January", "February", "March", _
"April", "May", "June", _
"July", "August", "September", _
"October", "November", "December")

On Error Resume Next
'delete summary sheet
Application.DisplayAlerts = False
Worksheets("Total").Delete
Application.DisplayAlerts = True
'insert new sheet
Set ws = Worksheets.Add
ws.Name = "Total"
'set up names and days
Worksheets(szMonths(1)).Range("A:A").Copy _
ws.Range("A1")
ws.Range("A3") = "Employee"
ws.Range("B3") = "Total"
For iCol = 3 To 27
ws.Cells(3, iCol) = "'" & iCol - 2
Next iCol
With ws.Range("A3:AA3")
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingleAccounting
End With
'main loop
For iEmp = 4 To 98
iCol = 3
ws.Range("B" & iEmp).Formula = "=COUNT(C" & iEmp & ":AA" & iEmp & ")"
For iMonth = 1 To 12
With Worksheets(szMonths(iMonth))
For iDay = 4 To 34
If .Cells(iEmp, iDay) = "H" Then
ws.Cells(iEmp, iCol) = DateSerial(2006, 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:

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

Thank you so much Martin
Absolutley perfect


"Martin Fishlock" wrote:

Ok, this is a macro and you copy the code below into a module in the vb
editior.
You do this by
Alt+F11 (open vb editor)
Insert Module
then paste it.

Go back to excel and
Alt+F8 to open macros and run makesummary

there is no need to insert a sheet as it does it automatically.

Have fun and please rate the reply to close it. thanks.


Option Explicit
Option Base 1


Sub makesummary()
Dim ws As Worksheet
Dim wsdata As Worksheet
Dim iCol As Integer
Dim iEmp As Integer
Dim iDay As Integer
Dim iMonth As Integer
Dim szMonths As Variant
szMonths = Array("January", "February", "March", _
"April", "May", "June", _
"July", "August", "September", _
"October", "November", "December")

On Error Resume Next
'delete summary sheet
Application.DisplayAlerts = False
Worksheets("Total").Delete
Application.DisplayAlerts = True
'insert new sheet
Set ws = Worksheets.Add
ws.Name = "Total"
'set up names and days
Worksheets(szMonths(1)).Range("A:A").Copy _
ws.Range("A1")
ws.Range("A3") = "Employee"
ws.Range("B3") = "Total"
For iCol = 3 To 27
ws.Cells(3, iCol) = "'" & iCol - 2
Next iCol
With ws.Range("A3:AA3")
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingleAccounting
End With
'main loop
For iEmp = 4 To 98
iCol = 3
ws.Range("B" & iEmp).Formula = "=COUNT(C" & iEmp & ":AA" & iEmp & ")"
For iMonth = 1 To 12
With Worksheets(szMonths(iMonth))
For iDay = 4 To 34
If .Cells(iEmp, iDay) = "H" Then
ws.Cells(iEmp, iCol) = DateSerial(2006, 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:

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