Populate Cells on Main Sheet with Cells from Several Sheets

[CathieGG]

I have a workbook that tracks employee hours. I need to give the owner a breakdown/total of Vacation, PTO, Sick Time and Other for each employee on a bi-weekly basis.

I have currently been entering the cells from each workbook page individually, but there must be an easier way. Any help is appreciated. Thanks

Payroll.xlsx
	A	B	C	D	E	F	G	H	I
1			Vacation	Sick	Personal		Total		Other
2
3	1/15/22	Jane Doe	0	0	0		0		0
4	1/29/22	Jane Doe	0	0	0		0		8
5	2/12/22	Jane Doe	0	0	0		0		0
6	2/26/22	Jane Doe	0	0	0		0		8
7	3/12/22	Jane Doe	0	0	0		0		0
8	3/26/22	Jane Doe	0	0	0		0		0
9	4/9/22	Jane Doe	0	0	0		0		0
10	4/23/22	Jane Doe	40	0	0		40		0
11	5/7/22	Jane Doe	0	0	0		0		0
12	5/21/22	Jane Doe	0	0	0		0		0
13	6/4/22	Jane Doe	0	0	0		0		8
14	6/18/22	Jane Doe	0	0	0		0		0
15	7/2/22	Jane Doe	8	0	0		8		8
16	7/16/22	Jane Doe	8	0	0		8		8
17	7/30/22	Jane Doe	0	0	0		0		0
18	8/13/22	Jane Doe	0	0	0		0		0
19	8/27/22	Jane Doe	0	0	0		0		0
20	9/10/22	Jane Doe					0
21	9/24/22	Jane Doe					0
22	10/8/22	Jane Doe					0
23	10/22/22	Jane Doe					0
24	11/5/22	Jane Doe					0
25	11/19/22	Jane Doe					0
26	12/3/22	Jane Doe					0
27	12/17/22	Jane Doe					0
28	12/31/22	Jane Doe					0
29							0
30
31		Danielle Anderson	56	0	0		56		40
YTD Calc

Cell Formulas
Range	Formula
C16:E16	C16	='Wk 07-16'!C27
C17:E17	C17	='Wk 07-30'!C27
C18:E18	C18	='Wk 08-13'!C26
C19:E19	C19	='Wk 8-27'!C25
I16	I16	='Wk 07-16'!F27
I17	I17	='Wk 07-30'!F27
I18	I18	='Wk 08-13'!F26
I19	I19	='Wk 8-27'!F25
A4:A28	A4	=A3+14
G3:G29	G3	=SUM(C3:E3)
I31,G31,C31:E31	C31	=SUM(C3:C30)

 

[RobP]

Hi,

its a little tricky to understand your need without seeing an example of your employee sheets, and then also what you are trying to do with a "summary" sheet as such ?

Its not clear if this sheet you post is the summary, or an individual employee sheet (for Jane Doe) ?

It would be good to see your RAW data for each employee (examples of course) that you enter manually for your tracking purpose. Then let people here know what you want doing with it exactly ?
Great you were able to post the sheet though - just need to see a few more of your overal file please.

Also, if we could understand the structure of your workbook (in terms of sheets, how they are named, by employee, or by week number etc..)
cheers
Rob

 

[CathieGG]

The sheet I am posting is the summary w/breakdown by pay period for each employee. Owner wants to see info without clicking every tab. So for Jane Doe I would import her cell values for Vacation, PTO, Sick Pay and Other from each tabbed location.

Here are the tab names

The info that I need to bring over to the main sheet is the same on each payroll tab.

Payroll.xlsx
	A	B	C	D	E	F	G
23		Payroll Ending 8/27/22
24		Regular	Vacation	Sick	Personal	Other	Total
25	Jane Doe	80	0	0	0	0	80
26	John Doe	80	0	0	0	0	80
27	Jane Smith	80	0	0	0	0	80
28	Joe Smith	80	0	0	0	0	80
29	Sam Smith	72	0	0	8	0	80
30	Tim Smith	78.5	0	0	0	0	78.5
31	Ed Smith	62.5	0	16	0	0	78.5
32	Mike Smith	77.5	0	0	1	0	78.5
33	Chris Smith	80	0	0	0	0	80
34	Chuck Smith	72	0	0	8	0	80
35	Sue Smith	80	0	0	0	0	80
36	Sue Doe	80	0	0	0	0	80
37	Ed Doe	73.42	0	7.75	7.75	0	88.92
38	Mike Doe	72	0	8	0	0	80
39	Kim Doe	49.5	0	0	0	0	49.5
40	Mark Doe	45.5	0	0	0	0	45.5
41		1162.92	0	31.75	24.75	0	1219.42
Wk 8-27

Cell Formulas
Range	Formula
B40:F40,B38:E39,C37:D37,B25:E36	B25	=B4+J4
F25:F39	F25	=F4+G4+N4+O4
G25:G40	G25	=SUM(B25:F25)
B37,E37	B37	=B16+J16+'Wk 09-10'!B16
B41:G41	B41	=SUM(B25:B40)

 

[RobP]

Hi, thanks for the data. having your data stored on individual sheets is not so great for getting summaries.. as you can see . So I tried to make it a little easier for you.

Create a New Sheet, called "YTD CALC" (note all capitals, not to overwrite your current "YTD Calc" sheet while you try it out.

On this sheet, you need to put your headings (C1 = "Regular", D1 = "Vacation", E1 = "Sick", F1 = "Personal", G1 = "Other", H1 = "Total") - note this is the same order as you have them on your bi-weekly sheets - very important. It's ok that they start in Col B in your bi-weekly sheets and in column C on "YTD CALC".

Instead of dates in your first column A, I have used the sheet name references as you have them, hoping thats ok - you'll see when you run it. The code also assumes that the data on each bi-weekly sheet starts at Row 25.


Then, you can insert this VBA code and run the macro. (Alt+F11 takes you to VBA editor, then just paste the code below to the into the blank box window on screen)

Sub main()


Dim WS_Count As Integer
Dim I As Integer
Dim nrows, ytdlastrow As Long
Dim shtRange As Variant

WS_Count = ActiveWorkbook.Worksheets.Count
ytdlastrow = 1 'first row of data will be added to row 2
       
For I = 1 To WS_Count 'Loop through each worksheet

    If (ActiveWorkbook.Worksheets(I).Name <> "YTD CALC" And ActiveWorkbook.Worksheets(I).Name <> "YTD Calc") Then 'ignore the "YTC CALC" summary sheet
            sheetlastrow = ActiveWorkbook.Worksheets(I).Cells(Rows.Count, 1).End(xlUp).Row
          
            shtRange = ActiveWorkbook.Worksheets(I).Range("A25", "G" & sheetlastrow)
            nrows = sheetlastrow + 1 - 25 'how many rows of data were grabbed
             
            ActiveWorkbook.Worksheets("YTD CALC").Range("A" & ytdlastrow + 1, "A" & ytdlastrow + nrows) = ActiveWorkbook.Worksheets(I).Name
            ActiveWorkbook.Worksheets("YTD CALC").Range("B" & ytdlastrow + 1, "H" & ytdlastrow + nrows) = shtRange
          
            ytdlastrow = Sheets("YTD CALC").Cells(Rows.Count, 1).End(xlUp).Row 'reset last row on YTD CALC
      
    End If
Next I

End Sub

Hopefully having all your data at least stored on 1 page can now allow you to use filtering (Ctrl+Shft+L) on your table to just show whom you want, and from which week ?
Each time you run the macro, it will effectively overwrite the YTD CALC sheet each time with each sheet of data you have, and add the last one to the end.

Let me know if its helpful.

cheers
Rob

 

[RobP]

Many thanks Cathie for the feedback. Glad to be of assistance.

Rob