Main Sheet

R

Ranjith Kurian

Hi,

I have more than two sheets and the first sheet is named as Main and the
other sheets are named as per employee names, everyday each person update
there work assigned status to the respective sheets, i need a macro to
consolidate the total count of PC column and AU column as per todays date to
main sheet as examples shown below

sheet Main:
EMP Date PC AU

sheet X:
Date PC AU
27-05-09
28-05-09 completed audit
29-05-09 completed audit
29-05-09

sheet Y:
Date PC AU
27-05-09
28-05-09 completed audit
29-05-09 completed audit
29-05-09 completed

Answer required is as below, i need the count of x and y sheets only todays
details, every day when i open main sheet it should show me only todays
information (yesterday data need to be replaced by today everday)

EMP Date PC AU
X 29-05-09 1 1
Y 29-05-09 2 1
 
J

Joel

Sub CopyToMain()

Set MainSht = Sheets("Main")
With MainSht
.Rows("2:" & Rows.Count).Delete
MainRow = 2
End With

For Each sht In Sheets
If UCase(sht.Name) <> "MAIN" Then
With sht
RowCount = 2
Do While .Range("A" & RowCount) <> ""

Employee = .Name
RowDate = .Range("A" & RowCount)
If RowDate = Date Then
MainSht.Range("A" & MainRow) = Employee
MainSht.Range("B" & MainRow) = Date
PC = .Range("B" & RowCount)
MainSht.Range("C" & MainRow) = PC
AU = .Range("C" & RowCount)
MainSht.Range("D" & MainRow) = AU
MainRow = MainRow + 1
End If
RowCount = RowCount + 1
Loop
End With
End If

Next sht


End Sub
 
R

Ranjith Kurian

Hi Joel,

Thanks for the code, its working fine, but its copying as it is from the
sheet X and Y to main sheet, but my requirement is that the main sheet should
show me the count example:
EMP Date PC AU
X 29-05-09 1 1
Y 29-05-09 2 1
The above example says that X emp have 1 completed status on 29th may and 1
audit status o 29th may.

and Y emp have 2 completed status on 29th may and 1 audit status o 29th may.
 
J

Joel

Sub CopyToMain()

Set MainSht = Sheets("Main")
With MainSht
.Rows("2:" & Rows.Count).Delete
MainRow = 2
End With

For Each sht In Sheets
If UCase(sht.Name) <> "MAIN" Then
With sht
PC_Count = 0
AU_Count = 0
Employee = .Name

Do While .Range("A" & RowCount) <> ""

RowDate = .Range("A" & RowCount)
If RowDate = Date Then
PC = .Range("B" & RowCount)
If UCase(PC) = "COMPLETED" Then
PC_Count = PC_Count + 1
End If

AU = .Range("C" & RowCount)
If UCase(AU) = "AUDIT" Then
AU_Count = AU_Count + 1
End If

End If
MainSht.Range("A" & MainRow) = Employee
MainSht.Range("B" & MainRow) = Date
MainSht.Range("C" & MainRow) = PC_Count
MainSht.Range("D" & MainRow) = AU_Count
MainRow = MainRow + 1

RowCount = RowCount + 1
Loop
End With
End If

Next sht


End Sub
 
R

Ranjith Kurian

Thanks a lot for the response
The count is working fine, but the count shown is wrong , the count is
inclusive of previous date and it has not sumed up

result shown from macro:

EMP Date PC AU
x 30-05-09 0 0
x 30-05-09 0 0
x 30-05-09 1 1
x 30-05-09 1 1
d 30-05-09 0 0
d 30-05-09 0 0
d 30-05-09 1 1
d 30-05-09 2 1


Result required is as shown below:
Sheet Main:
EMP Date PC AU
x 30-05-09 1 1
d 30-05-09 2 1

Sheet x:
Date PC AU
27-05-09
28-05-09 completed audit
30-05-09 completed audit
30-05-09

Sheet d:
Date PC AU
27-05-09
28-05-09 completed audit
30-05-09 completed audit
30-05-09 completed
 
J

Joel

I put the code to write to the main worksheet in the wrong place. Try this

Sub CopyToMain()

Set MainSht = Sheets("Main")
With MainSht
.Rows("2:" & Rows.Count).Delete
MainRow = 2
End With

For Each sht In Sheets
If UCase(sht.Name) <> "MAIN" Then
With sht
PC_Count = 0
AU_Count = 0
Employee = .Name
RowCount = 2

Do While .Range("A" & RowCount) <> ""

RowDate = .Range("A" & RowCount)
If RowDate = Date Then
PC = .Range("B" & RowCount)
If UCase(PC) = "COMPLETED" Then
PC_Count = PC_Count + 1
End If

AU = .Range("C" & RowCount)
If UCase(AU) = "AUDIT" Then
AU_Count = AU_Count + 1
End If

End If

RowCount = RowCount + 1
Loop
MainSht.Range("A" & MainRow) = Employee
MainSht.Range("B" & MainRow) = Date
MainSht.Range("C" & MainRow) = PC_Count
MainSht.Range("D" & MainRow) = AU_Count
MainRow = MainRow + 1
End With
End If

Next sht

End Sub
 
R

Ranjith Kurian

Thanks a lot, its working wonderfull.


Joel said:
I put the code to write to the main worksheet in the wrong place. Try this

Sub CopyToMain()

Set MainSht = Sheets("Main")
With MainSht
.Rows("2:" & Rows.Count).Delete
MainRow = 2
End With

For Each sht In Sheets
If UCase(sht.Name) <> "MAIN" Then
With sht
PC_Count = 0
AU_Count = 0
Employee = .Name
RowCount = 2

Do While .Range("A" & RowCount) <> ""

RowDate = .Range("A" & RowCount)
If RowDate = Date Then
PC = .Range("B" & RowCount)
If UCase(PC) = "COMPLETED" Then
PC_Count = PC_Count + 1
End If

AU = .Range("C" & RowCount)
If UCase(AU) = "AUDIT" Then
AU_Count = AU_Count + 1
End If

End If

RowCount = RowCount + 1
Loop
MainSht.Range("A" & MainRow) = Employee
MainSht.Range("B" & MainRow) = Date
MainSht.Range("C" & MainRow) = PC_Count
MainSht.Range("D" & MainRow) = AU_Count
MainRow = MainRow + 1
End With
End If

Next sht

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top