PDA

View Full Version : Help with Sorting by first name



menor59
03-06-2013, 12:52 PM
Please see that attached Link

https://docs.google.com/file/d/0B1BS5RUbGx58T1NuUUlJaVBGRDg/edit?usp=sharing


The Names are in C2, C13, C24, and C35, Im showing 4 but i have 50 employees that need to be sorted by first name.

If possible can someone help with a VBA to Sort those Tables By First name?

Name C2 has B2:J11
C13 has B13:J22
C24 has B24:J33
C35 has B35:J44

there are 50 of theses.

Thank you!!

Basically something like this...

Start:

https://docs.google.com/file/d/0B1BS5RUbGx58TUVCYS1rcWY0Zlk/edit?usp=sharing


end result:

https://docs.google.com/file/d/0B1BS5RUbGx58ajl2YWl5cEE0Rms/edit?usp=sharing

GTO
03-07-2013, 01:25 AM
Greetings menor59:hi:

Firstly, let me welcome you to the site! I've been a member for several years now, and am sure you will enjoy the help you can get here as much as I do.

I would mention that you will get faster responses if we don't have to go to some other storage site to download an attachment. Frankly, a lot of these are blocked at plenty of worksites, including mine. Anyways, I was able to get the attachment, so here's a stab at maybe what you are looking to do?

I did not try sorting the tables in place, as copying (sorted) seemed easier.

In a Standard Module:
Option Explicit

Sub example()
Dim DIC As Object ' Scripting.Dictionary
Dim rngLRN As Range
Dim arrNames As Variant
Dim arrRows As Variant
Dim arrOutput As Variant
Dim lLastRecordNameRow As Long
Dim lCurRow As Long
Dim n As Long

'// Temp "safety" in case <Cancel> //
On Error Resume Next
Set rngLRN = Application.InputBox( _
Prompt:="Select the cell with the name of the last record...", _
Title:=vbNullString, _
Type:=8)
On Error GoTo 0

If rngLRN Is Nothing Then Exit Sub

lLastRecordNameRow = rngLRN.Row

'// Something to hopefully ensure the tables are equally spaced/have same no. of rows.//
If Sheet1.Range("C" & lLastRecordNameRow).Offset(9).Row Mod 11 = 0 Then

Set DIC = CreateObject("Scripting.Dictionary")

'// I am assuming no duplicate names, and including the last name. Since our sort//
'// will compare left to right, Bob Smith would end up before Bob Wills. //
For lCurRow = Sheet1.Range("C" & lLastRecordNameRow).Row To 2 Step -11
DIC.Item(UCase(Trim(Sheet1.Cells(lCurRow, 3).Value))) = lCurRow
Next

'// Plunk .Items (the row number) and .Keys(the name) into two parrallel arrays //
'// (presuming Late-Bound), and then loop these into a single two-column array. /
arrNames = DIC.Keys
arrRows = DIC.Items
ReDim arrOutput(0 To UBound(arrNames), 0 To 1)

For n = 0 To UBound(arrOutput, 1)
arrOutput(n, 0) = arrNames(n)
arrOutput(n, 1) = arrRows(n)
Next

'// Sort our array. //
BubbleSort arrOutput

lCurRow = 2
'// Now running through our sorted array, we can use the original row numbers to//
'// know which cell (resized to the table) to copy to another sheet. //
For n = LBound(arrOutput, 1) To UBound(arrOutput, 1)
Sheet1.Range("B" & arrOutput(n, 1)).Resize(10, 9).Copy Sheet2.Cells(lCurRow, "B")
lCurRow = lCurRow + 11
Next
End If
End Sub

' Contrived from an example BubbleSort at:
' http://www.xtremevbtalk.com/showpost.php?postid=386990&postcount=2
Function BubbleSort(ByRef vntIOArray)
Dim lOuter As Long, lInner As Long, lLBnd As Long, lUBnd As Long, Tmp(0 To 1) As Variant

lLBnd = LBound(vntIOArray, 1)
lUBnd = UBound(vntIOArray, 1)

For lOuter = lLBnd To lUBnd - 1
For lInner = lLBnd To lUBnd - lOuter - 1
If vntIOArray(lInner, 0) > vntIOArray(lInner + 1, 0) Then
Tmp(0) = vntIOArray(lInner, 0)
Tmp(1) = vntIOArray(lInner, 1)

vntIOArray(lInner, 0) = vntIOArray(lInner + 1, 0)
vntIOArray(lInner, 1) = vntIOArray(lInner + 1, 1)

vntIOArray(lInner + 1, 0) = Tmp(0)
vntIOArray(lInner + 1, 1) = Tmp(1)
End If
Next lInner
Next lOuter
End Function

If you look at the attached, you can see the difference between the sheet's CodeName and Worksheet name.

Hope thatg helps,

Mark

Doug Robbins
03-07-2013, 02:50 AM
Assuming that the data is in Sheet1, the following code will make a copy of that sheet as Sheet1 (2) with the data sorted as required. (It should handle all of your employees)

Sub sort()
Dim strnames As String
Dim varnames As Variant
Dim varnamesandrows()
Dim rnganchor As Range
Dim rngtarget As Range
Dim lngOffset As Long
Set rnganchor = Worksheets(1).Range("C1")
Dim i As Long, j As Long, k As Long, n As Long
i = 0
strnames = ""
Do While rnganchor.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rnganchor.Offset(11 * i + 1, 0)
i = i + 1
Loop
strnames = Mid(strnames, 2)
varnames = Split(strnames, "|")
j = Val(Format(UBound(varnames)))
ReDim varnamesandrows(j, 1)
For i = LBound(varnames) To UBound(varnames)
varnamesandrows(i, 0) = varnames(i)
varnamesandrows(i, 1) = 11 * i + 1
Next i
Call BubbleSort(varnamesandrows)
Sheets("Sheet1").Copy Before:=Sheets(2)
Set rngtarget = Worksheets("Sheet1 (2)").Range("C1")
For i = LBound(varnames) To UBound(varnames)
rngtarget.Offset(11 * i + 1, 0).Value = varnamesandrows(i, 0)
n = varnamesandrows(i, 1)
For j = 1 To 7
For k = 1 To 6
rngtarget.Offset(11 * i + 1 + j, k).Value = rnganchor.Offset(n + j, k).Value
Next k
Next j
Next i
End Sub
Sub BubbleSort(arr)
Dim strTemp1 As String
Dim strTemp2 As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
For j = i + 1 To lngMax
If arr(i, 0) > arr(j, 0) Then
strTemp1 = arr(i, 0)
strTemp2 = arr(i, 1)
arr(i, 0) = arr(j, 0)
arr(i, 1) = arr(j, 1)
arr(j, 0) = strTemp1
arr(j, 1) = strTemp2
End If
Next j
Next i
End Sub

Bob Phillips
03-07-2013, 02:53 AM
Doug, can you add VBA tags around your code? There is a green VBA button above the reply textbox that adds them automatically.

menor59
03-07-2013, 11:54 AM
Read below...Im sorry

menor59
03-07-2013, 11:56 AM
IM Sorry all Typo:
what i originally stated...

The Names are in C2, C13, C24, and C35, Im showing 4 but i have 50 employees that need to be sorted by first name.

If possible can someone help with a VBA to Sort those Tables By First name?

Name C2 has B2:J11
C13 has B13:J22
C24 has B24:J33
C35 has B35:J44

there are 50 of theses.

Thank you!!

Basically something like this...

Should read:

The Names are in C12, C23, C24, and C35, Im showing 4 but i have 50 employees that need to be sorted by first name.

If possible can someone help with a VBA to Sort those Tables By First name?

Name C12 has B12:J21
C23 has B23:J32
C34 has B34:J43
C45 has B45:J54

there are 50 of theses.

Thank you!!

Basically something like this...

can you please modify your code above?

Doug Robbins
03-07-2013, 01:41 PM
In my code, replace

Set rnganchor = Worksheets(1).Range("C1")


with

Set rnganchor = Worksheets(1).Range("C11")


and replace

Set rngtarget = Worksheets("Sheet1 (2)").Range("C1")


with

Set rngtarget = Worksheets("Sheet1 (2)").Range("C11")

menor59
03-09-2013, 06:16 PM
Doug,

So i added your Code to mine...

My Code...which is in Sheet1 (Blank)


Sub Copy_Sheet()
If Range("A2").Value >= Range("A1").Value Then
MsgBox "This Date you Selected has passed...Please Click Undo, and delete the Tab Below Labeled " & Sheets(1).Range("X12").Text
Else
End If

Dim wSht As Worksheet
Dim shtName As String
shtName = Sheets(1).Range("X12")
For Each wSht In Worksheets
If wSht.Name = shtName Then
MsgBox "Sheet already exists...Use the Button " & _
"on the TAB titled BLANK!" & _
" **Remember to select a New Date!"
Exit Sub
End If
Next wSht
Sheets(1).Copy Before:=Sheets(1)
Sheets(1).Name = shtName
Sheets(shtName).Move After:=Sheets(Sheets.Count)
End Sub


Your Code:


Sub sort()
Dim strnames As String
Dim varnames As Variant
Dim varnamesandrows()
Dim rnganchor As Range
Dim rngtarget As Range
Dim lngOffset As Long
Set rnganchor = Worksheets(1).Range("C12")
Dim i As Long, j As Long, k As Long, n As Long
i = 0
strnames = ""
Do While rnganchor.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rnganchor.Offset(11 * i + 1, 0)
i = i + 1
Loop
strnames = Mid(strnames, 2)
varnames = Split(strnames, "|")
j = Val(Format(UBound(varnames)))
ReDim varnamesandrows(j, 1)
For i = LBound(varnames) To UBound(varnames)
varnamesandrows(i, 0) = varnames(i)
varnamesandrows(i, 1) = 11 * i + 1
Next i
Call BubbleSort(varnamesandrows)
Sheets("Sheet1").Copy Before:=Sheets(2)
Set rngtarget = Worksheets("Sheet1 (2)").Range("C12")
For i = LBound(varnames) To UBound(varnames)
rngtarget.Offset(11 * i + 1, 0).Value = varnamesandrows(i, 0)
n = varnamesandrows(i, 1)
For j = 1 To 7
For k = 1 To 6
rngtarget.Offset(11 * i + 1 + j, k).Value = rnganchor.Offset(n + j, k).Value
Next k
Next j
Next i
End Sub
Sub BubbleSort(arr)
Dim strTemp1 As String
Dim strTemp2 As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
For j = i + 1 To lngMax
If arr(i, 0) > arr(j, 0) Then
strTemp1 = arr(i, 0)
strTemp2 = arr(i, 1)
arr(i, 0) = arr(j, 0)
arr(i, 1) = arr(j, 1)
arr(j, 0) = strTemp1
arr(j, 1) = strTemp2
End If
Next j
Next i
End Sub


soooo...

what my code does is this...

when the workbook is opened theres a worksheet called "Blank".
on that work sheet theres pull down (data validation) in C2 (which is referenced to X12 in format MMM dd, yyyy) that has every 1st Sunday in the year...when my code above is activated based on the date in the pull down window, my code above creates a new worksheet based on the value in X12 (the MMM dd, YYYY) value in X12. can you make your code work with mine....make sense sir...ohhh and thank you!!!

when putting your code in with mine it errors out with

Compile error:
Member already exists in an object module from which this object module derives....

Is it possible for you to try your code by doing the following...

Create a workbook, name one worksheet Blank
delete sheet2 and 3
Put My code in ALT-F11 of the Blank worksheet...

In cell X12 put the date of Mar 10, 2013 in that format...

run my code.....

it should have created a new work sheet entitled Mar 10, 2013 keeping the Blank intact (thats basically the template)

Now delete the work sheet entitles Mar 10, 2013 and put your code after mine...and run the codes...

Doug Robbins
03-09-2013, 07:00 PM
Put all of the code in a standard module (Insert>Module in the VBE) rather than in Sheet 1.

menor59
03-09-2013, 07:02 PM
will my code continue to work as before??

menor59
03-09-2013, 07:05 PM
ok its breaking here....



Sheets("Sheet1").Copy Before:=Sheets(2)


did you see my code before??? above?

menor59
03-09-2013, 07:14 PM
Doug can you PM me your email address so i can send it you you?

menor59
03-09-2013, 07:19 PM
Doug can you PM me your email address if your ok with it?

menor59
03-09-2013, 09:27 PM
OK Doug,

the code works but i just noticed its moving the names in Column C

rephrase...

it needs to use the Names are in C12, C23, C24, and C35, etc etc...for sorting....

and needs to arrange the chart also following the name...ie...


so when sorting based on names in C12, C23, C34, C45 etc. etc....

it must include the range around it....

C12 has the name to sort...then select and move B12:J21
C23 has the name to sort...then select and move B23:J32
C34 has the name to sort...then select and move B34:J43
C45 has the name to sort...then select and move B45:J54
etc.etc..

the B's - J's is a chart with the C's having names...


so lets say the following....

C12 = Darth Vader something in D14 B12:J21
C23 = harry potter something in F27 B23:J32
c34 = Cory feldman something in I40 B34:J43
c45 = luke skywalker something in H46 B45:J54

running the module would do the following...

c12 = Cory feldman something in I18 B12:J21
c23 = Darth Vader something in D25 B23:J32
c34 = Harry Potter something in F38 B34:J43
C45 = Luke Skywalker something in H46 B45:J54

Picture each name has a Boarder around it... the B12:J21, B23:J32 , b34:j43 , and B45:j54 and the names are in each border at c12, c23, c34, c45.... based on the name is needs to sort and move the border and whats inside the border along with the name....

does that help??

menor59
03-09-2013, 10:14 PM
Here is a Before:

https://docs.google.com/file/d/0B1BS5RUbGx58T1YwZjE0X0VEZnc/edit?usp=sharing


Heres an after:

https://docs.google.com/file/d/0B1BS5RUbGx58QVZTdHFmcXZ0aU0/edit?usp=sharing

ive also modified you code slightly...

please notice that han solo and his table moved in order of the other 3



Sub sort()
Dim strnames As String
Dim varnames As Variant
Dim varnamesandrows()
Dim rnganchor As Range
Dim rngtarget As Range
Dim lngOffset As Long
Set rnganchor = ActiveSheet.Range("C11")
Dim i As Long, j As Long, k As Long, n As Long
i = 0
strnames = ""
Do While rnganchor.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rnganchor.Offset(11 * i + 1, 0)
i = i + 1
Loop
strnames = Mid(strnames, 2)
varnames = Split(strnames, "|")
j = Val(Format(UBound(varnames)))
ReDim varnamesandrows(j, 1)
For i = LBound(varnames) To UBound(varnames)
varnamesandrows(i, 0) = varnames(i)
varnamesandrows(i, 1) = 11 * i + 1
Next i
Call BubbleSort(varnamesandrows)
Set rngtarget = ActiveSheet.Range("C11")
For i = LBound(varnames) To UBound(varnames)
rngtarget.Offset(11 * i + 1, 0).Value = varnamesandrows(i, 0)
n = varnamesandrows(i, 1)
For j = 1 To 7
For k = 1 To 6
rngtarget.Offset(11 * i + 1 + j, k).Value = rnganchor.Offset(n + j, k).Value
Next k
Next j
Next i
End Sub
Sub BubbleSort(arr)
Dim strTemp1 As String
Dim strTemp2 As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
For j = i + 1 To lngMax
If arr(i, 0) > arr(j, 0) Then
strTemp1 = arr(i, 0)
strTemp2 = arr(i, 1)
arr(i, 0) = arr(j, 0)
arr(i, 1) = arr(j, 1)
arr(j, 0) = strTemp1
arr(j, 1) = strTemp2
End If
Next j
Next i
End Sub

Doug Robbins
03-10-2013, 01:13 AM
Use the following:

Sub sort()
Dim strnames As String
Dim varnames As Variant
Dim varnamesandrows()
Dim rng1 As Range
Dim rng2 As Range
Dim lngOffset As Long
Dim newSheet As Worksheet
Dim origSheet As Worksheet
Set origSheet = ActiveSheet
Set rng1 = origSheet.Range("C11")
Dim i As Long, j As Long, k As Long, n As Long
i = 0
strnames = ""
Do While rng1.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rng1.Offset(11 * i + 1, 0)
i = i + 1
Loop
strnames = Mid(strnames, 2)
varnames = Split(strnames, "|")
j = Val(Format(UBound(varnames)))
ReDim varnamesandrows(j, 1)
For i = LBound(varnames) To UBound(varnames)
varnamesandrows(i, 0) = varnames(i)
varnamesandrows(i, 1) = 11 * i + 1
Next i
Call BubbleSort(varnamesandrows)
Set newSheet = Sheets.Add
origSheet.UsedRange.Copy
newSheet.Paste
Set rng2 = newSheet.Range("C11")
For i = LBound(varnames) To UBound(varnames)
rng1.Offset(11 * i + 1, 0).Value = varnamesandrows(i, 0)
n = varnamesandrows(i, 1)
For j = 0 To 8
For k = 1 To 6
rng1.Offset(11 * i + 1 + j, k).Value = rng2.Offset(n + j, k).Value
Next k
Next j
Next i
Application.DisplayAlerts = False
newSheet.Delete
Application.DisplayAlerts = True
End Sub
Sub BubbleSort(arr)
Dim strTemp1 As String
Dim strTemp2 As String
Dim i As Long
Dim j As Long
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = lngMin To lngMax - 1
For j = i + 1 To lngMax
If arr(i, 0) > arr(j, 0) Then
strTemp1 = arr(i, 0)
strTemp2 = arr(i, 1)
arr(i, 0) = arr(j, 0)
arr(i, 1) = arr(j, 1)
arr(j, 0) = strTemp1
arr(j, 1) = strTemp2
End If
Next j
Next i
End Sub

menor59
03-10-2013, 01:53 AM
Almost..

So uI created one entry and populated the cells with numbers...the green area...when i ran the macro it put the names correctly...but from the new name down...it also populated the same numbers i inputed into the user....see the enclosed sample...
https://docs.google.com/file/d/0B1BS5RUbGx58YUYxN2FFVzZwVVE/edit?usp=sharing

snb
03-10-2013, 03:38 AM
Why don't you upload your file here ?
This forum has been designed to provide this facility and is much more robust than the site you posted it.

You can keep it simple using:

Sub M_snb()
With CreateObject("System.Collections.ArrayList")
For j = 12 To 45 Step 11
.Add Sheet1.Cells(j, 3).Value
Next
.sort
sn = .toarray
End With

For j = 0 To UBound(sn)
Sheet1.Cells(12 + 11 * j, 30).Resize(10, 9) = Sheet1.Columns(3).Find(sn(j), , xlValues, 1).Offset(, -1).Resize(10, 9).Value
Next
End Sub

Doug Robbins
03-10-2013, 04:06 AM
Attached is an After file created by running the code in the workbook on the Before file that you posted at:

https://docs.google.com/file/d/0B1BS5RUbGx58T1YwZjE0X0VEZnc/edit?usp=sharing


As in the file that you posted at:


https://docs.google.com/file/d/0B1BS5RUbGx58YUYxN2FFVzZwVVE/edit?usp=sharing

you have reinstated the formula to sum the Total Hours, I have done the same in the Before file that this was created from and as a result have changed the


For j = 0 to 8


to


For j = 0 to 7


Otherwise, the code is the same as that in my most recent post.

snb
03-10-2013, 04:28 AM
Or use the builtin sorting facility for named ranges:

Sub M_snb_001()
For j = 12 To 45 Step 11
Sheet1.Cells(j, 3).Offset(, -1).Resize(10, 9).Name = Replace(Sheet1.Cells(j, 3).Value, " ", "_")
Next

For j = 1 To Application.Names.Count
Sheet1.Cells(12 + 11 * (j - 1), 30).Resize(10, 9) = Application.Names(j).RefersToRange.Value
Next
End Sub

and if you want to preserve the formatting:
Sub M_snb_002()
For j = 12 To 45 Step 11
Sheet1.Cells(j, 3).Offset(, -1).Resize(10, 9).Name = Replace(Sheet1.Cells(j, 3).Value, " ", "_")
Next

For j = 1 To Application.Names.Count
Application.Names(j).RefersToRange.Copy Sheet1.Cells(12 + 11 * (j - 1), 30)
Next
End Sub

Doug Robbins
03-10-2013, 02:38 PM
Hi snb,

It think I understand what your code is doing, but you may note from my responses that I was using:

Do While rng1.Offset(11 * i + 1, 0) <> ""
strnames = strnames & "|" & rng1.Offset(11 * i + 1, 0)
i = i + 1
Loop


to construct an Array of the names. I was doing that because, while Menor59's example only contained times sheets for 4 names, in his original post, he mentioned having 50 employees.

I'm sure your code can be modified to deal with a varying number of timesheets, but I am not yet that confident in my understanding of it to suggest the modification.

snb
03-10-2013, 03:53 PM
to expand it's functionality:



Sub M_snb_003()
For j = 12 To sheet1.usedrange.rows.count Step 11
if Sheet1.Cells(j, 3)<>"" Then Sheet1.Cells(j, 3).Offset(, -1).Resize(10, 9).Name = Replace(Sheet1.Cells(j, 3).Value, " ", "_")
Next

For j = 1 To Application.Names.Count
Application.Names(j).RefersToRange.Copy Sheet1.Cells(12 + 11 * (j - 1), 30)
Next
End Sub

menor59
03-11-2013, 12:58 PM
Doug,

It works Like a charm....

But when its run it removes my Formulas that are in the Respective Table...

Its removing the formulas in the Total hours

see attached...

Doug Robbins
03-11-2013, 02:14 PM
In the After.xlsm file that I included with my post of 03-10-2013 at 09:06pm which was created by the code in that file, the formulae in the Total Hours cells are retained. As I noted in that post, as you had reinstated the formulae to those cells (they weren't there in your earlier workbooks) I had reinstated them in the source file and I had also changed the line of code

For j = 0 To 8

to

For j = 0 To 7

so that the code does NOT do anything with that row.

Your Payroll Employees.xlsm, contains

For j = 0 To 8

and it is because of that you no longer have the formulae.

menor59
03-11-2013, 02:49 PM
Got it...Thank you doug....Ready for one more Hurdle???

how would I get all the hours and the dollar amount for each employee for the Month based from the 1st day of the month thru the last day of the month as well as their name once from all sheets onto the Report for (AA17) sheet starting on cell C5 (do not include the Blank worksheet because its a template) ?

I already have a vba that creates the sheet



Private Sub Workbook_Open()
Worksheets("Blank").Activate
'Run "HideRibbon"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet, csheet As Worksheet
Application.ScreenUpdating = False
Set csheet = ActiveSheet
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible Then
sht.Activate
Range("B2").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
Next sht
csheet.Activate
Application.ScreenUpdating = True

Dim ws As Worksheet
Dim pwd As String
'pwd = "5"
For Each ws In Worksheets
ws.protect Password:=pwd
Next ws
'Run "HideRibbon"
End Sub


Now that sheet needs to search all tabed weeks bring in the Names once and find names that arent on the previous sheet... (ie employees added..in the middle of the month...) then total the Hours based on the 1st day and the last day of that month for the employees. as well as amounts....(All the stuff in Yellow on my worksheets....)

Ive attached a sample...

Doug Robbins
03-11-2013, 07:58 PM
You really need to be more explicit in formulating your requests.

What really does the following mean:

"from all sheets onto the Report for (AA17) sheet starting on cell C5"

There is nothing in Cell AA17 on the Report sheet.

It has also got to the point where if I had realised from the beginning the whole story of what you are trying to do, I would have suggested that you should be recording the individual times in a table in an Access database and it then would have been possible to produce reports in whatever manner you required.

menor59
03-11-2013, 08:04 PM
"from all sheets onto the Report for (AA17) sheet starting on cell C5"


In My sample (attachment) there is a Tab called

"Report for (AA17)"
AA17 is a Date DD MMM, yyyy from the blank sheet

This macro creates the Sheet on Open



Option Explicit
Private Sub Workbook_Open()
Worksheets("Blank").Activate
'Run "HideRibbon"
Dim h As String
h = "Report for "
h = h & Format(Sheets("Blank").Range("AA17"), "MMM dd, yyyy")
If SheetExists(h) = False Then Worksheets.Add(After:=Sheets("Blank")).Name = h
Sheets(h).Activate
End Sub

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function




so the tab is truly called Report for Mar 31, 2013

On that sheet All the names from the Previous tabs....

(each tab for the month is named as the first sunday)...(also in my sample)

Take all the Names from all the tabs and put them on the "Report for XXXX Tab" then put them on C5 Down... if the name apprears 5 times on the week tabs...put it once on the report tab...etc etc..

D5 downtake all the hours and

E5 Down total earnings

ive attached it again

Doug Robbins
03-11-2013, 09:19 PM
Take another look at the way your sheets are named. They are named for the first Sunday + 7

menor59
03-11-2013, 09:24 PM
and there is one sheet called report for "the last day of the month"

On that sheet...find and put all names from existing sheets excluding blank
also add all the Hours and place on that sheet for each employee for the month
and add all the earnings and place on that sheet for each employee...