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)
VBA Code:
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