Log data from timecard into table in separate worksheet

[Mdb]

I have a timecard which has data and a layout looking like the
following (timecard is on a sheet called "Time Card" and the log sheet
is called "Log"... the dates are formula-generated):


Employee Name [cell C9]

Sun Mon Tues Wed Thurs Fri
Sat

Jan 1 Jan 2 Jan 3 Jan 4 Jan 5 Jan 6
Jan 7

**two rows of info not required to be filtered**

Stat Holiday 8
JobNumber1 4 6 8
JobNumber2 4
JobNumber3 2 8
etc...

I have been trying with no solution, to come up with a way to filter
and re-organized the timecard info into a log table which would filter
out dates in which there is no hours input (all blank cells) and filter
out the rows which were not used at all. For example, my timecard table
consists of a range from D18:J32 (below the days of the week) to fill
out weekly hours. All rows may not be used depending on how many jobs
were worked on. Additionally, not all jobs are worked on every day
(example above). I'm trying to filter the info so that the above
information would look like and be input to the log sheet similar to
the following:

Week Day Date Job# Hrs. Employee

Monday Jan 2 Stat Holiday 8 Name
Tuesday Jan 3 JobNumber1 4 Name
Tuesday Jan 3 JobNumber2 4 Name
Wednesday Jan 4 JobNumber1 6 Name
Wednesday Jan 4 JobNumber3 2 Name
Thursday Jan 5 JobNumber1 8 Name
Friday Jan 6 JobNumber3 8 Name

Of course, I'm also trying to have it record to the next empty cell in
the "Log" worksheet so it won't overwrite any previous data.

I realize this might be a major undertaking, but I appreciate anyone
who might take it on. I'm fairly new to excel and VBA and I've been
fumbling through for a long time, unable to come up with a solution.
Maybe this is too difficult a thing to do.

Appreciated,
Mike db

 

[okrob]

I've been working on a similiar task. I have to enter time daily for
multiple people. It 'logs' the daily data to an accumulation sheet and
saves a copy of the timesheet with the date as the sheet name.
I'll send it to you when I get to work tomorrow. I'm sure that you can
play with it and get it to work for your application.
The code is complex and the entire workbook is about 1mb, but for what
you want to do, that can be cut dramatically.
I have a pivot table that updates every time a timesheet is processed
so you can keep track of project times etc...
Like I said, you'll at least have a start and some great code (lots
from this group!!!) to learn from.

Rob

I have a timecard which has data and a layout looking like the
following (timecard is on a sheet called "Time Card" and the log sheet
is called "Log"... the dates are formula-generated):


Employee Name [cell C9]

Sun Mon Tues Wed Thurs Fri
Sat

Jan 1 Jan 2 Jan 3 Jan 4 Jan 5 Jan 6
Jan 7

**two rows of info not required to be filtered**

Stat Holiday 8
JobNumber1 4 6 8
JobNumber2 4
JobNumber3 2 8
etc...

I have been trying with no solution, to come up with a way to filter
and re-organized the timecard info into a log table which would filter
out dates in which there is no hours input (all blank cells) and filter
out the rows which were not used at all. For example, my timecard table
consists of a range from D18:J32 (below the days of the week) to fill
out weekly hours. All rows may not be used depending on how many jobs
were worked on. Additionally, not all jobs are worked on every day
(example above). I'm trying to filter the info so that the above
information would look like and be input to the log sheet similar to
the following:

Week Day Date Job# Hrs. Employee

Monday Jan 2 Stat Holiday 8 Name
Tuesday Jan 3 JobNumber1 4 Name
Tuesday Jan 3 JobNumber2 4 Name
Wednesday Jan 4 JobNumber1 6 Name
Wednesday Jan 4 JobNumber3 2 Name
Thursday Jan 5 JobNumber1 8 Name
Friday Jan 6 JobNumber3 8 Name

Of course, I'm also trying to have it record to the next empty cell in
the "Log" worksheet so it won't overwrite any previous data.

I realize this might be a major undertaking, but I appreciate anyone
who might take it on. I'm fairly new to excel and VBA and I've been
fumbling through for a long time, unable to come up with a solution.
Maybe this is too difficult a thing to do.

Appreciated,
Mike db

 

[okrob]

You should now have the workbook. Please let me know what you think
and whether or not you can use it.
Rob

I've been working on a similiar task. I have to enter time daily for
multiple people. It 'logs' the daily data to an accumulation sheet and
saves a copy of the timesheet with the date as the sheet name.
I'll send it to you when I get to work tomorrow. I'm sure that you can
play with it and get it to work for your application.
The code is complex and the entire workbook is about 1mb, but for what
you want to do, that can be cut dramatically.
I have a pivot table that updates every time a timesheet is processed
so you can keep track of project times etc...
Like I said, you'll at least have a start and some great code (lots
from this group!!!) to learn from.

Rob

I have a timecard which has data and a layout looking like the
following (timecard is on a sheet called "Time Card" and the log sheet
is called "Log"... the dates are formula-generated):


Employee Name [cell C9]

Sun Mon Tues Wed Thurs Fri
Sat

Jan 1 Jan 2 Jan 3 Jan 4 Jan 5 Jan 6
Jan 7

**two rows of info not required to be filtered**

Stat Holiday 8
JobNumber1 4 6 8
JobNumber2 4
JobNumber3 2 8
etc...

I have been trying with no solution, to come up with a way to filter
and re-organized the timecard info into a log table which would filter
out dates in which there is no hours input (all blank cells) and filter
out the rows which were not used at all. For example, my timecard table
consists of a range from D18:J32 (below the days of the week) to fill
out weekly hours. All rows may not be used depending on how many jobs
were worked on. Additionally, not all jobs are worked on every day
(example above). I'm trying to filter the info so that the above
information would look like and be input to the log sheet similar to
the following:

Week Day Date Job# Hrs. Employee

Monday Jan 2 Stat Holiday 8 Name
Tuesday Jan 3 JobNumber1 4 Name
Tuesday Jan 3 JobNumber2 4 Name
Wednesday Jan 4 JobNumber1 6 Name
Wednesday Jan 4 JobNumber3 2 Name
Thursday Jan 5 JobNumber1 8 Name
Friday Jan 6 JobNumber3 8 Name

Of course, I'm also trying to have it record to the next empty cell in
the "Log" worksheet so it won't overwrite any previous data.

I realize this might be a major undertaking, but I appreciate anyone
who might take it on. I'm fairly new to excel and VBA and I've been
fumbling through for a long time, unable to come up with a solution.
Maybe this is too difficult a thing to do.

Appreciated,
Mike db

 

[Mdb]

I appreciate it. Thanks.

Too complex for me.