I hate to keep asking for help, but I can't get this on my own. :-(

[TAPS_MikeDion]

Hi all,

I have a TOTALS sheet that needs to pull data from several sheets and I just can't seem to get this right. Sorry, I wish I could get this on my own, but I'm stumped. I'm thinking this is going to be a VBA project.

Here are the sheets in the workbook (table examples are below):

• EMPLOYEES
  • 2 Columns: First Name, Last Name
  • Up to 75 rows
• LOCRATE
  • 3 Columns: Location Name, Location Abbrev (3-letters), Pay Rate
  • # of rows are based on however many locations
• 17 (3-letter) Location sheets (i.e BGE, PAH, PDD, and so on)
  • Each location sheet has the employees names (linked to from the EMPLOYEES sheet)
  • 34 Columns: Last Name, First Name, WAL (Works At Location), 1-31 (Days of the month)
  • Up to 75 rows (# of employees)
• SUMMARY
• Employees names (linked to from the EMPLOYEES sheet)
  • 20 Columns: Last Name, First Name, Fixed Pay, 17 location sheet names
  • Up to 75 rows (# of employees)
• TOTALS
  • Here's where I'm having the trouble
  • Employees names (linked to from the EMPLOYEES sheet)
  • 8 Columns: Last Name (A), First Name (B), Location (C), Location Hourly Rate (D), Hours Worked (E), Total Hours Worked (F), Pay (G)
  • # of rows will always vary

In the TOTALS sheet:

• I need to search through all rows in the SUMMARYsheet...
  • Put the employee names from the SUMMARY sheet columns A & B in the TOTALS sheet columns A & B
  • Going down the rows in the SUMMARYsheet for each employee
    • Find every occurence of an hours total in a location column of the SUMMARY sheet
    • If hours were found in any location column
      • Look in the LOCRATE sheet for the "Location Name"
        • Find the matching the 3-letter code in LOCRATE column B that matches the SUMMARY sheet location column 3-letter name
        • Using that row in the LOCRATE sheet...
        • Put column A data (location name) in column C of the TOTALS sheet
        • Put column C data (Pay Rate) in column D of the TOTALS sheet
    • Put the hours worked total from the SUMMARY sheet into column E
    • When all SUMMARY sheet location columns (17 columns) have been gone through, then...
• Column F in the TOTALS sheet needs to total up all column E entries for that employee
• Column G then calculates the "Pay"...
  • Going down the rows for that employee
    • Multiply column D & column E amounts

Hopefully looking at the table3s makes it easier to follow.

EMPLOYEES sheet

LAST NAME	FIRST NAME
Doe	Jane
West	Tom
Gates	Bill

LOCRATE sheet

LOCATION NAME	LOCATION ABBREV	PAY RATE
Willy Wonka's Chocolate Factory	WWC	15
Joe's Window Wash	JWW	12

WWC (3-Letter Location) sheet (only went from day 1 to 5 to keep it short)
WAL = Works At Location
Employee 1 worked 24 hours at this location from the 1st to the 3rd of the month
Employee 2 does not work at this location
Employee 3 worked 16 hours at this location from the 4th to the 5th of the month

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
=employee sheet A2	=employee sheet B2	x	8	8	8
=employee sheet A3	=employee sheet B3
=employee sheet A4	=employee sheet B4	x				8	8

JWW (3-Letter Location) sheet (only went from day 1 to 5 to keep it short)
WAL = Works At Location
Employee 1 does not work at this location
Employee 2 worked 24 hours at this location on the 1st, 3rd and 5th of the month
Employee 3 worked 16 hours at this location on the 2nd & 4th of the month

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
=employee sheet A2	=employee sheet B2
=employee sheet A3	=employee sheet B3	x	8		8		8
=employee sheet A4	=employee sheet B4	x		8		8

SUMMARY sheet
For argument sake (Tom West - employee sheet A3) is on fixed pay - that can be disregarded in any code written.

LAST NAME	FIRST NAME	FIXED PAY	WWC	JWW	ETC
=employee sheet A2	=employee sheet B2		24
=employee sheet A3	=employee sheet B3	x		24
=employee sheet A4	=employee sheet B4		16	16

TOTALS sheet
Using the information pulled in from the other sheets of the workbook...
Columns F & G in the 4th line are just empty cells since the TOTAL HOURS WORKED & PAY are filled in at the bottom of Bill Gates' rows of info.

LAST NAME	FIRST NAME	LOCATION	LOCATION HOURLY RATE	HOURS WORKED	TOTAL HOURS WORKED	PAY
Doe	Jane	Willy Wonka's Chocolate Factory	15	24	24	$360.00
West	Tom	Joe's Window Wash	12	24	24	$288.00
Gates	Bill	Willy Wonka's Chocolate Factory	15	16	LEAVE BLANK - NO DATA	LEAVE BLANK - NO DATA
Gates	Bill	Joe's Window Wash	12	16	32	$432.00 (240+192)

Hopefully this is enough info and not too confusing for whomever agrees to help me out on this.

THANK YOU IN ADVANCE!!!

I know this is a lot to ask for, but I don't know enough about VBA to do this one on my own.

 

[TAPS_MikeDion]

You worked on it already!?

 

[TAPS_MikeDion]

.

 

[hernantorres23]

.

Yes, I wrote notes in dropbox

 

[hernantorres23]

Done.
Although I do not totally agree with your design, I have managed to do what you want.
Please check the formula, and change this in your final workbook and also you must adjust correctly conditional format in every sheets
I'm so sorry, for some spanish comment, but in some cases, I mix my native languaje naturally while declare variables and do comments inside the code

Book1
	A	B	C	D	E	F	G	H	I	J
1	codEmp	LAST NAME	FIRST NAME	WAL	1	2	3	4	5
2	1	Doe	Jane		8	8	8
3	2	West	Tom
4	3	Gates	Bill					8	8
5	4	North	Larry
6	5	Quincy	John
7	6	Adams	Sam
8
WWC

Cell Formulas
Range	Formula
A2:C7	A2	=EMPLOYEES!A2

Book1
	A	B	C	D	E	F	G	H	I	J
1	codEmp	LAST NAME	FIRST NAME	WAL	1	2	3	4	5
2	1	Doe	Jane
3	2	West	Tom		8		8		8
4	3	Gates	Bill			8		8
5	4	North	Larry
6	5	Quincy	John
7	6	Adams	Sam
8
JWW

Cell Formulas
Range	Formula
A2:C7	A2	=EMPLOYEES!A2

Book1
	A	B	C	D	E	F	G	H	I	J
1	codEmp	LAST NAME	FIRST NAME	WAL	1	2	3	4	5
2	1	Doe	Jane		6		1		9
3	2	West	Tom			2	8		8
4	3	Gates	Bill			4		8
5	4	North	Larry
6	5	Quincy	John			10		4
7	6	Adams	Sam		2		6
8
ETC

Cell Formulas
Range	Formula
A2:C7	A2	=EMPLOYEES!A2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	codEmp	LAST NAME	FIRST NAME	FIXED PAY	WWC	JWW	ETC
2	1	Doe	Jane		24	0	16
3	2	West	Tom	x	0	24	18
4	3	Gates	Bill		16	16	12
5	4	North	Larry		0	0	0
6	5	Quincy	John		0	0	14
7	6	Adams	Sam		0	0	8
8
9
10										You formula is too long and
11										not necessary, I replace them
12
13
SUMMARY

Cell Formulas
Range	Formula
A2:C7	A2	=EMPLOYEES!A2
D2:D7	D2	=IF(INDEX(EMPLOYEES!$A$2:$D$7,MATCH(SUMMARY!A2,EMPLOYEES!$A$2:$A$7,0),4),"x","")
E2:G2, F3:G7	E2	=SUM(INDIRECT(E$1&"!E"&ROW($A2)&":AI"&ROW($A2)))
E3:E7	E3	=SUM(INDIRECT($E$1&"!E"&ROW(A3)&":AI"&ROW(A3)))

Result ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	codEmp	LAST NAME	FIRST NAME	LOCATION	LOCATION HOURLY RATE		HOURS WORKED	TOTAL HRS WORKED	PAY
2	1	Doe	Jane	Willy Wonka's Chocolate Factory	15		24
3	1	Doe	Jane	Etcetera	23		16	40	728
4	2	West	Tom	Joe's Window Wash	12		24
5	2	West	Tom	Etcetera	23		18	42	702
6	3	Gates	Bill	Willy Wonka's Chocolate Factory	15		16
7	3	Gates	Bill	Joe's Window Wash	12		16
8	3	Gates	Bill	Etcetera	23		12	44	708
9	5	Quincy	John	Etcetera	23		14	14	322
10	6	Adams	Sam	Etcetera	23		8	8	184
11
TOTALS

Sub payroll_report()
Dim h, i, r As Long
Dim b(), a(), z() As Variant

Application.ScreenUpdating = False
'first clean range
Sheets("TOTALS").Activate 
If Range("A" & Rows.Count).End(xlUp).Row > 1 Then
Range("A2:I" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents
End If
  Sheets("SUMMARY").Activate 'activeshhet
  b = Sheets("SUMMARY").Range("A2:G" & Range("G" & Rows.Count).End(xlUp).Row).Value2 'change this range or put dinamically number columns
  a = Array("WWC", "JWW", "ETC") 'tambien podrias hacerlo dinamico
  For r = 1 To UBound(b)
  i = 5 'inicio en la columna 5 hasta la 7 de la hoja SUMMARY
      For h = LBound(a) To UBound(a)
         If b(r, i) <> 0 Then
         ReDim z(9) 'los ocho(8) campos del reporte
         z(0) = b(r, 1)
         z(1) = b(r, 2)
         z(2) = b(r, 3)
         'z(2) = Cells(Application.Match(a(h), Sheet3.Range("A2:A" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row), 0) + 1, "A").Offset(0, 2)
         z(3) = Application.Index(Range("LocAndRates"), Application.Match(a(h), Range("LocAndRates[LOCATION ABBREV]"), 0), 2)
         z(4) = Application.Index(Range("LocAndRates"), Application.Match(a(h), Range("LocAndRates[LOCATION ABBREV]"), 0), 3)
         z(5) = Empty
         z(6) = b(r, i)
         'MsgBox b(r, 2) & " " & b(r, 3) & " has worked for " & b(r, i) & " hours in " & a(h), vbInformation, "Mikel ERP by htorres"
         Sheets("TOTALS").Activate
         Range(Cells(Range("A" & Rows.Count).End(xlUp).Row + 1, "A"), Cells(Range("A" & Rows.Count).End(xlUp).Row + 1, "G")).FormulaArray = z()
         End If
         i = i + 1
      Next h
  Next r

'Run ("totalizador") 'this code summarize hours and total payment, like you want it
Application.ScreenUpdating = True
MsgBox "Payroll report completed", vbInformation, "Mikel ERP by htorres"
Sheets("TOTALS").Range("L1").Activate
End Sub

 

[TAPS_MikeDion]

Hi Hernan,

Thank you so much for this! I'm sorry, I had a very busy weekend and I still have not had a chance to go through any of this yet. I have no problem trying any suggestions to change the layout of the workbook. It started as a minor project and turned into a huge one.

I'll look things over as soon as I can and let you know how everything went.

Thanks again,
Mike

 

[hernantorres23]

Also check in Dropbox, there are several notes, I can share Ms Access projects with you

 

[TAPS_MikeDion]

Hi Hernan,

I was checking the Dropbox file to see the work you had done, but it wasn't showing any changes on my end. I thought maybe you just wanted me to copy and paste the VBA in your last post. I did, but when it runs it gets to the end with the MsgBox ("Payroll report completed") and then I get a Run-Time Error 1004 in the last line of the code before the End Sub. Also, after the code runs, there is no data in the TOTALS sheet.

Please let me know what I'm doing wrong.

Thanks again, and Feliz Navidad!

 

[hernantorres23]

Now, I haven't my PC, but when I get home, I will post a link with full example.