Hi,

Right i have a worbook that has 6 tab sheets, these are named:

2005
2006
2007
2008
2009
2010

On each tab sheet is an attendance register where a user can fill in
which staff are absent, which are in and late and so on and so forth.

i have a 7th tab sheet, on this tab sheet is a report which counts the
amount of absences, in's, lates per staff member from the other 6
sheets. There are also 6 radio buttons,

2005
2006
2007
2008
2009
2010

when a user clicks on one of these radio buttons it changes the values
in the report to report on that year.

Seeing as the report uses the same code for each year (the only thing
that differs from code to code is the tab sheet of which the COUNTIF
statements refference) i was wandering is there a way to set the year
to say X and make X a variable and then depending on which radio button
is selected would depend on which value X would be:

2005, 2006, 2007, 2008, 2009 or 2010

So for example for one report the COUNTIF statement looks like this:


Code:
--------------------

Range("B5").Formula = "=(COUNTIF('2005'!D6:D370," & Chr(34) & "IN" & Chr(34) & ")) + (COUNTIF('2005'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) + (COUNTIF('2005'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) + (COUNTIF('2005'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

--------------------


and then for the 2006 tab it would look like:


Code:
--------------------

Range("B5").Formula = "=(COUNTIF('2006'!D6:D370," & Chr(34) & "IN" & Chr(34) & ")) + (COUNTIF('2006'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) + (COUNTIF('2006'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) + (COUNTIF('2006'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

--------------------


so they are the same apart from the refferenced tab sheet of which is
the year. So instead of writing out this code 6 times over is there
not a way to write it out once with a variable instead of the tab sheet
and then depending on which radio button is pushed depends on what the
variable is set to?

If anyone could provide any code it would be extremely helpful.
Thanks!


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=480792

sStr "=(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "IN" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('ZZZZ'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

Range("B5").Formula = Replace(sStr,"ZZZZ","2005")

--
Regards,
Tom Ogilvy



"alymcmorland"
wrote in message
news:alymcmorland.1xteib_1130843108.8756@excelforu m-nospam.com...

Hi,

Right i have a worbook that has 6 tab sheets, these are named:

2005
2006
2007
2008
2009
2010

On each tab sheet is an attendance register where a user can fill in
which staff are absent, which are in and late and so on and so forth.

i have a 7th tab sheet, on this tab sheet is a report which counts the
amount of absences, in's, lates per staff member from the other 6
sheets. There are also 6 radio buttons,

2005
2006
2007
2008
2009
2010

when a user clicks on one of these radio buttons it changes the values
in the report to report on that year.

Seeing as the report uses the same code for each year (the only thing
that differs from code to code is the tab sheet of which the COUNTIF
statements refference) i was wandering is there a way to set the year
to say X and make X a variable and then depending on which radio button
is selected would depend on which value X would be:

2005, 2006, 2007, 2008, 2009 or 2010

So for example for one report the COUNTIF statement looks like this:


Code:
--------------------

Range("B5").Formula = "=(COUNTIF('2005'!D6:D370," & Chr(34) & "IN" &
Chr(34) & ")) + (COUNTIF('2005'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('2005'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('2005'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

--------------------


and then for the 2006 tab it would look like:


Code:
--------------------

Range("B5").Formula = "=(COUNTIF('2006'!D6:D370," & Chr(34) & "IN" &
Chr(34) & ")) + (COUNTIF('2006'!D6:D370," & Chr(34) & "L" & Chr(34) & ")) +
(COUNTIF('2006'!D6:D370," & Chr(34) & "OT" & Chr(34) & ")) +
(COUNTIF('2006'!D6:D370," & Chr(34) & "BackIN" & Chr(34) & "))"

--------------------


so they are the same apart from the refferenced tab sheet of which is
the year. So instead of writing out this code 6 times over is there
not a way to write it out once with a variable instead of the tab sheet
and then depending on which radio button is pushed depends on what the
variable is set to?

If anyone could provide any code it would be extremely helpful.
Thanks!


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=480792

Hi, if i want to choose a tab sheet by the date would i put something
like:


Code:
--------------------

Sub Macro_DropRef()

Get Date
zzzz = Date
Sheets("DropRefzzzz").Select

End Sub

--------------------


I know thats wrong above, could anyone correct my mistake(s)?!


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=480792

You have to concatenate

Sub Macro_DropRef()
z = Date
na = "DropRef " & z
Sheets(na).Select
End Sub

Ole Michelsen

"alymcmorland"
wrote in message
news:alymcmorland.1xz04m_1131104402.6107@excelforu m-nospam.com...

Hi, if i want to choose a tab sheet by the date would i put something
like:


Code:
--------------------

Sub Macro_DropRef()

Get Date
zzzz = Date
Sheets("DropRefzzzz").Select

End Sub

--------------------


I know thats wrong above, could anyone correct my mistake(s)?!


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile:
http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=480792

That returned the error of subscript out of range, and when i debugged
it the highlighted line is:

Sheets(na).Select


--
alymcmorland
------------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...o&userid=27652
View this thread: http://www.excelforum.com/showthread...hreadid=480792

Can you help with checking that error or maybe giving some idea as t
why it would say that

--
alymcmorlan
-----------------------------------------------------------------------
alymcmorland's Profile: http://www.excelforum.com/member.php...fo&userid=2765
View this thread: http://www.excelforum.com/showthread.php?threadid=48079