Hello everyone,

I have a spreadsheet that is used to track employee absence. At the end of
each year accounting will take the sheet and archive it. Since we hire new
people we are adding sheets throughout the year. I need to add a second
archive button that can effect only specific sheets. What I want to do is
take the date of hire (C5) and compare it to a date I will hard code into
the macro. If the date of hire is before the hard coded date I want the
program to copy and past the absences to a lower position in the sheet. Then
clear out the absences from the top. I will be placing this button on the
main sheet so punching it will have to search all tabs in the sheet and
check that cell.

the value C5 is pointing to the main sheet. Which is an index of the
employees and is there for quick viewing. I don't know how to go from sheet
to sheet with the macro.

All help is deeply appreciated.
LWhite

dim wks as worksheet
for each wks in activeworkbook.wks
'do what you need to to wks
if wks.range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
next wks

or maybe....

dim wks as worksheet
for each wks in activeworkbook.wks
if lcase(wks.name) = lcase("main") then
'do nothing
else
'do what you need to to wks
if worksheets("main").range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
end if
next wks

====
I'm confused about what's in C5 and where it is???

"L.White" wrote:

Hello everyone,

I have a spreadsheet that is used to track employee absence. At the end of
each year accounting will take the sheet and archive it. Since we hire new
people we are adding sheets throughout the year. I need to add a second
archive button that can effect only specific sheets. What I want to do is
take the date of hire (C5) and compare it to a date I will hard code into
the macro. If the date of hire is before the hard coded date I want the
program to copy and past the absences to a lower position in the sheet. Then
clear out the absences from the top. I will be placing this button on the
main sheet so punching it will have to search all tabs in the sheet and
check that cell.

the value C5 is pointing to the main sheet. Which is an index of the
employees and is there for quick viewing. I don't know how to go from sheet
to sheet with the macro.

All help is deeply appreciated.
LWhite

--

Dave Peterson

Thanks Dave.

Each sheet has the following value in C5.

='Index'!B21

Index is the name of the master tab of the sheet that shows the totals
from each individual employee. In column B is each employees date of
hire.

Leonard

Dave Peterson wrote:
dim wks as worksheet
for each wks in activeworkbook.wks
'do what you need to to wks
if wks.range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
next wks

or maybe....

dim wks as worksheet
for each wks in activeworkbook.wks
if lcase(wks.name) = lcase("main") then
'do nothing
else
'do what you need to to wks
if worksheets("main").range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
end if
next wks

====
I'm confused about what's in C5 and where it is???


Dave Peterson

So you could use the first suggestion and check the "local" copy.

Or you could use the second suggestion and check the value on the index
worksheet (with a minor modification):

if worksheets("main").range("C5").value < dateserial(2005,1,31) then
becomes
if worksheets("index").range("b21").value < dateserial(2005,1,31) then

I would think that they would work the same.

LWhite wrote:

Thanks Dave.

Each sheet has the following value in C5.

='Index'!B21

Index is the name of the master tab of the sheet that shows the totals
from each individual employee. In column B is each employees date of
hire.

Leonard

Dave Peterson wrote:
dim wks as worksheet
for each wks in activeworkbook.wks
'do what you need to to wks
if wks.range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
next wks

or maybe....

dim wks as worksheet
for each wks in activeworkbook.wks
if lcase(wks.name) = lcase("main") then
'do nothing
else
'do what you need to to wks
if worksheets("main").range("C5").value < dateserial(2005,1,31) then
'do the copy/move
end if
end if
next wks

====
I'm confused about what's in C5 and where it is???


Dave Peterson

--

Dave Peterson