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