T
theargus
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 are:
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
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 are:
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