I currently update this workbook weekly - it is a very manual,
laborious, and time consuming process. I am not very well versed in
VBA and I am curious as to whether or not automation is a viable
solution for this - if it is it would sure save me TONS of time.

I will explain this as best I can.

This workbook tracks Employee hours for each project number they worked
on, ORG number they worked under, and pay scale (GLC) they worked
under. More on this below.

There are 3 worksheets involved - Sheet A, Sheet 1, and Sheet 2. Data
is taken from Sheet A and pasted into Sheet 1. After ALL of the data
from Sheet A is put into Sheet 1, THEN data is taken from Sheet 1 and
pasted into Sheet 2. So, the worksheet relationships a

Sheet A Sheet 1
Sheet 1 Sheet 2

I currently put Sheet A into it's own workbook and the other 2
worksheets into THEIR own workbook, but consolidating them all into one
workbook is definitely an option - I would just need to delete Sheet A
out of the workbook before sending the finished product to my
superiors.


"Sheet A" is dumped into Excel from a reporting program called
Impromptu and formatted by running various macros to delete columns,
fill in blank cells, center columns, correct font size, and delete
header rows. Sheet A contains the weekly data that I have to
incorporate into the other two worksheets. After formatting, Sheet A
is arranged as follows:


SHEET A
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A1.

Column A = Employee Name (Last, First)
Column B = Org Number (ex. 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 19 31 18 2
- Al, Roy 1.2. 101797 11 32 7 7
- Ma, Nil 1.6. 100421 20 21 8 1
- Po, Bob 1.5. 101897 21 38 7 4
- Po, Bob 1.2. 100427 10 31 8 1


As you can see, sometimes individuals have multiple rows in Sheet A -
this indicates that they worked on differing Org # / Project # / GLC
#'s.

Sheet A will ALWAYS have exactly 7 columns. There will always be
exactly 3 "hours" columns (which always represent hours for the current
pay period).


Next is Sheet 1:


SHEET 1
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A7 (rows 1 through 6 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Org Number (ex, 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns E F and G represent hours for one week - Columns H I and J
would represent hours for the NEXT week, and so forth (see 2nd visual
representation below).


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 14 32 10 8
- Al, Roy 1.4. 101782 19 30 2 2
- Na, Bil 1.7. 100421 20 21 8 1
- Mo, Rob 1.8. 101897 21 15 7 4
- Mo, Rob 1.1. 100427 10 2 8 1



VISUAL REPRESENTATION of "hours" columns:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | ST Hours | TH Hours | DT Hours | ST Hours | TH Hours | DT Hours |
- 32 10 8 37 2 10
- 38 2 2 28 14 3


As you can see, each week another 3 "hours" columns are added to Sheet
1.

So the data from Sheet A has to be incorporated into Sheet 1 - several
conditions determine the exact way in which it needs to be pasted in.

* * Under every possible condition, only the VALUES from Sheet A should
be pasted into Sheet 1 as Sheet 1 contains formatting (coloring,
borders) that needs to remain intact.

AUTOMATION GOALS FOR SHEET A SHEET 1:

CONDITIONS:

IF a row in Sheet A contains row(s) for an employee that does NOT
already have an entry in Sheet 1, then the entire row(s) would be
pasted into Sheet 1 alphabetically by Column A (Employee Name - Last
Name, First Name) - AND the 3 "hours" cells would need to be pasted
into the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 1 AND worked ONLY
under the EXACT SAME Org #, Project #, and GLC #, as before, the only
information that would need to be carried over from Sheet A would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week. Again,
this only applies if the Org #, Project #, and GLC # data ALL match
exactly.

IF a specific employee already has an entry in Sheet 1 AND worked under
a DIFFERENT Org #, Project #, OR GLC # than before, then the entire
row(s) would be pasted into Sheet 1 alphabetically by Column A
(Employee Name - Last Name, First Name) - AND the 3 "hours" cells would
need to be pasted into the CORRECT columns for THAT WEEK.



Now onto Sheet 2:

SHEET 2
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A6 (rows 1 through 5 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Straight Time Hours (ex. 32)
Column C = Time and a Half Hours (ex. 12)
Column D = Double Time Hours (ex. 11)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns B C and D represent hours for one week - Columns E F and G
represent hours for the NEXT week, and so forth (see visual
representation below).


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | Name | ST Hrs | TH Hrs | DT Hrs | ST Hrs | TH Hrs | DT Hrs | TWTO*|
- Al, Roy 12 12 10 65 16 13 2
- Na, Bil 21 8 1 39 1 1 2
- Mo, Rob 17 15 5 35 12 18 2


* Total Weeks With Time Off

As you can see, Sheet 2 contains only the Employee Name, the "hours"
data, and a column called "Total Weeks With Time Off". The "TWTO"
column calculates how many 3 column "hours" entries each employee has
in THIS PARTICULAR SHEET (2). This column would move over 3 columns
every week as the weekly 3 "hours" columns are added.

AUTOMATION GOALS FOR SHEET 1 SHEET 2:

CONDITIONS:

IF a row in Sheet 1 contains data for an employee that does NOT already
have an entry in Sheet 2, then the name cell and hours cells would be
pasted into Sheet 2 alphabetically by Column A (Employee Name - Last
Name, First Name) - the 3 "hours" cells would need to be pasted into
the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 2, the only
information that would need to be carried over from Sheet 1 would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week.


I apologize for the long-windedness.

Is this easily automatable?

The assistance that the contributors of this group provide is
invaluable. Any and all help is sincerely appreciated!
Thanks so much!


- Kobi

Is this automatable...Yes. Is it a good project for a beginner... No. There
are lots of conditions and loops to deal with, which would make this tricky.
One thing you could howerver investigate is pivot tables. The would allow you
to take the data from Sheet A and report it in formats similar to Sheets one
and two. Not to mention giving you a whole pile more flexibility to present
the data in a varitey of other formats. In your Sheet A though you would
probably want to have the date of the pay period week. If you can be a little
flexible on the output this is really easy to accomplish. With my years of
experience this is definitely the way I would go. It is Fast, Easy and a LOT
more powerful that what you are doing now. Trying it would only take 30
minutes maybe an hour of your time... Let me know if you want some help with
trying this and I will walk you through it...

HTH

" wrote:

I currently update this workbook weekly - it is a very manual,
laborious, and time consuming process. I am not very well versed in
VBA and I am curious as to whether or not automation is a viable
solution for this - if it is it would sure save me TONS of time.

I will explain this as best I can.

This workbook tracks Employee hours for each project number they worked
on, ORG number they worked under, and pay scale (GLC) they worked
under. More on this below.

There are 3 worksheets involved - Sheet A, Sheet 1, and Sheet 2. Data
is taken from Sheet A and pasted into Sheet 1. After ALL of the data
from Sheet A is put into Sheet 1, THEN data is taken from Sheet 1 and
pasted into Sheet 2. So, the worksheet relationships a

Sheet A Sheet 1
Sheet 1 Sheet 2

I currently put Sheet A into it's own workbook and the other 2
worksheets into THEIR own workbook, but consolidating them all into one
workbook is definitely an option - I would just need to delete Sheet A
out of the workbook before sending the finished product to my
superiors.


"Sheet A" is dumped into Excel from a reporting program called
Impromptu and formatted by running various macros to delete columns,
fill in blank cells, center columns, correct font size, and delete
header rows. Sheet A contains the weekly data that I have to
incorporate into the other two worksheets. After formatting, Sheet A
is arranged as follows:


SHEET A
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A1.

Column A = Employee Name (Last, First)
Column B = Org Number (ex. 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 19 31 18 2
- Al, Roy 1.2. 101797 11 32 7 7
- Ma, Nil 1.6. 100421 20 21 8 1
- Po, Bob 1.5. 101897 21 38 7 4
- Po, Bob 1.2. 100427 10 31 8 1


As you can see, sometimes individuals have multiple rows in Sheet A -
this indicates that they worked on differing Org # / Project # / GLC
#'s.

Sheet A will ALWAYS have exactly 7 columns. There will always be
exactly 3 "hours" columns (which always represent hours for the current
pay period).


Next is Sheet 1:


SHEET 1
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A7 (rows 1 through 6 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Org Number (ex, 1.0.01.01.01.1.34.1)
Column C = Project Number (ex. 200039)
Column D = GLC # (ex. 12)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns E F and G represent hours for one week - Columns H I and J
would represent hours for the NEXT week, and so forth (see 2nd visual
representation below).


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- | Name | Org# | Project# | GLC# | ST Hours | TH Hours | DT Hours |

- Al, Roy 1.0. 101444 14 32 10 8
- Al, Roy 1.4. 101782 19 30 2 2
- Na, Bil 1.7. 100421 20 21 8 1
- Mo, Rob 1.8. 101897 21 15 7 4
- Mo, Rob 1.1. 100427 10 2 8 1



VISUAL REPRESENTATION of "hours" columns:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | ST Hours | TH Hours | DT Hours | ST Hours | TH Hours | DT Hours |
- 32 10 8 37 2 10
- 38 2 2 28 14 3


As you can see, each week another 3 "hours" columns are added to Sheet
1.

So the data from Sheet A has to be incorporated into Sheet 1 - several
conditions determine the exact way in which it needs to be pasted in.

* * Under every possible condition, only the VALUES from Sheet A should
be pasted into Sheet 1 as Sheet 1 contains formatting (coloring,
borders) that needs to remain intact.

AUTOMATION GOALS FOR SHEET A SHEET 1:

CONDITIONS:

IF a row in Sheet A contains row(s) for an employee that does NOT
already have an entry in Sheet 1, then the entire row(s) would be
pasted into Sheet 1 alphabetically by Column A (Employee Name - Last
Name, First Name) - AND the 3 "hours" cells would need to be pasted
into the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 1 AND worked ONLY
under the EXACT SAME Org #, Project #, and GLC #, as before, the only
information that would need to be carried over from Sheet A would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week. Again,
this only applies if the Org #, Project #, and GLC # data ALL match
exactly.

IF a specific employee already has an entry in Sheet 1 AND worked under
a DIFFERENT Org #, Project #, OR GLC # than before, then the entire
row(s) would be pasted into Sheet 1 alphabetically by Column A
(Employee Name - Last Name, First Name) - AND the 3 "hours" cells would
need to be pasted into the CORRECT columns for THAT WEEK.



Now onto Sheet 2:

SHEET 2
Sorted alphabetically by Column A (Last Name, First Name)
Data starts in A6 (rows 1 through 5 contain Page Title Information and
Column Names).

Column A = Employee Name (Last, First)
Column B = Straight Time Hours (ex. 32)
Column C = Time and a Half Hours (ex. 12)
Column D = Double Time Hours (ex. 11)
Column E = Straight Time Hours (ex. 32)
Column F = Time and a Half Hours (ex. 12)
Column G = Double Time Hours (ex. 11)

** Columns B C and D represent hours for one week - Columns E F and G
represent hours for the NEXT week, and so forth (see visual
representation below).


VISUAL REPRESENTATION:

NOTE: The dashes on the left side don't exist in the worksheet - I
added them to this post only to correct the spacing:


- WEEK ENDING 1/08/2005 WEEK ENDING 1/15/2005
- | Name | ST Hrs | TH Hrs | DT Hrs | ST Hrs | TH Hrs | DT Hrs | TWTO*|
- Al, Roy 12 12 10 65 16 13 2
- Na, Bil 21 8 1 39 1 1 2
- Mo, Rob 17 15 5 35 12 18 2


* Total Weeks With Time Off

As you can see, Sheet 2 contains only the Employee Name, the "hours"
data, and a column called "Total Weeks With Time Off". The "TWTO"
column calculates how many 3 column "hours" entries each employee has
in THIS PARTICULAR SHEET (2). This column would move over 3 columns
every week as the weekly 3 "hours" columns are added.

AUTOMATION GOALS FOR SHEET 1 SHEET 2:

CONDITIONS:

IF a row in Sheet 1 contains data for an employee that does NOT already
have an entry in Sheet 2, then the name cell and hours cells would be
pasted into Sheet 2 alphabetically by Column A (Employee Name - Last
Name, First Name) - the 3 "hours" cells would need to be pasted into
the CORRECT columns for THAT WEEK.

IF a specific employee already has an entry in Sheet 2, the only
information that would need to be carried over from Sheet 1 would be
the data from the 3 "hours" columns - and that data would need to be
placed into the correct 3 columns for THAT particular week.


I apologize for the long-windedness.

Is this easily automatable?

The assistance that the contributors of this group provide is
invaluable. Any and all help is sincerely appreciated!
Thanks so much!


- Kobi

Hi Jim,

I SINCERELY appreicate your extremely prompt and informative
response.

I have a bit of experience with Pivot Tables, and am absolutely
interested in getting some help trying this out.
Let me know what you think - and THANKS AGAIN!


- Kobi

What version of Excel Are you on. It makes a difference as pivot tables have
changed. Here are the basics though. The data that you are exporting is
perfect for pivoting. Sheet A need to have column headers. Employee name...
which if I am correct you already have. To get the full power out of the
pivot table you want to not throw any data away. Just keep appending the new
information to the bottom of sheet A. You will have to add in the Dates as I
suggested earlier. Place the cursor anywhere in the middle of the data and
select Data - Pivot Table. A wizard will come up. You can just select finish
as Excel will probably make all of the correct decisions for you. You will
get a new sheet created with a table on the page and the Pivot Table Tool Bar
displayed. You can just drag the headings onto the table. Employees in the
right column. Hours in the middle. You can drag more than one heading
(dimension) into the middle, or the right colmn or top row. Anything you have
added can be moved around by just dragging it. You can group on the date
heading wich will allow you to group by month, quarter year or whatever...

This should get you started...

HTH

" wrote:

Hi Jim,

I SINCERELY appreicate your extremely prompt and informative
response.

I have a bit of experience with Pivot Tables, and am absolutely
interested in getting some help trying this out.
Let me know what you think - and THANKS AGAIN!


- Kobi

Jim Thomlinson wrote:
What version of Excel Are you on.

2002 (10.4302.4219) SP-2


It makes a difference as pivot tables have
changed. Here are the basics though. The data that you are exporting
is
perfect for pivoting. Sheet A need to have column headers. Employee
name...
which if I am correct you already have. To get the full power out of
the
pivot table you want to not throw any data away. Just keep appending
the new
information to the bottom of sheet A. You will have to add in the
Dates as I
suggested earlier. Place the cursor anywhere in the middle of the
data and
select Data - Pivot Table. A wizard will come up. You can just
select finish
as Excel will probably make all of the correct decisions for you. You
will
get a new sheet created with a table on the page and the Pivot Table
Tool Bar
displayed. You can just drag the headings onto the table. Employees
in the
right column. Hours in the middle. You can drag more than one heading

(dimension) into the middle, or the right colmn or top row. Anything
you have
added can be moved around by just dragging it. You can group on the
date
heading wich will allow you to group by month, quarter year or
whatever...

This should get you started...

HTH

THANKS JIM!


- Kobi