Solved: Trying to create a summary sheet

[Hoopsah]

Hi,

I have attached a file on here which is basically a listing of jobs that our advisors have been assigned.

I am trying to create a summary sheet that will only show the lines for any job that has been opened more than 21 days, and give a count at the bottom (i.e. Gordon Vickers = 0, Andrew Henderson = 5 etc)

Anyone got any ideas??

Thanks

G

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim col As Collection
Dim sh As Worksheet
Dim itm

With ActiveSheet

Set sh = Nothing
On Error Resume Next
Set sh = Worksheets("Summary")
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add
sh.Name = "Summary"
End If
sh.Cells.ClearContents

Set col = New Collection
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Rows(1).Copy sh.Range("A1")
NextRow = 1
For i = 2 To LastRow

If .Cells(i, "M").Value > 21 Then

NextRow = NextRow + 1
.Rows(i).Copy sh.Cells(NextRow, "A")
On Error Resume Next
col.Add .Cells(i, "D").Value, .Cells(i, "D").Value
On Error GoTo 0
End If
Next i

LastRow = NextRow
NextRow = NextRow + 2
For Each itm In col

NextRow = NextRow + 1
sh.Cells(NextRow, "D").Value = itm
sh.Cells(NextRow, "E").Value = "=COUNTIF(D1" & LastRow & ",D" & NextRow & ")"
Next itm
End With

End Sub
[/vba]

[Hoopsah]

hi XLD,

I tried this but all I got was a new sheet being created called summary.

Am I missing something or doing something wrong?

[Bob Phillips]

Well I don't know why. It works fine for me with 2003 and 2007.

[Bob Phillips]

Thought, did you have the correct sheet activated.

[Hoopsah]

Sorry XLD,

I am kinda new to this.

I created a macro, input your data, and ran it.

All I got was a Summary Sheet being created??

[Bob Phillips]

Post the updated workbook?

[Hoopsah]

ok,

It's a bit bigger than the original posting,

[Bob Phillips]

The idea was to post the updated workbook, that is including the code that I gave you. I just plugged the code in and it worked fine, so you are clearly doing something wrong, but I cannot tell from afar.

Did you add the code to a standard code module?

[Hoopsah]

Right, I am feeling very thick here !)

I have now attached the report with your module included. I have set it as a Macro, that when run, crerates a new sheet - thats all.

[Bob Phillips]

When you provide details of the data, it MUST conform to the layout of the real data, otherwise it ain't gonna work as the code will be dependent upon that layout.

[vba]

Private Const COL_TEST_NAME As String = "D"
Private Const COL_DAYS_OPEN As String = "N"

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long
Dim col As Collection
Dim sh As Worksheet
Dim itm

With ActiveSheet

Set sh = Nothing
On Error Resume Next
Set sh = Worksheets("Summary")
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add
sh.Name = "Summary"
End If
sh.Cells.ClearContents

Set col = New Collection
LastRow = .Cells(.Rows.Count, COL_TEST_NAME).End(xlUp).Row
.Rows(1).Copy sh.Range("A1")
NextRow = 1
For i = 5 To LastRow

If .Cells(i, COL_DAYS_OPEN).Value > 21 Then

NextRow = NextRow + 1
.Rows(i).Copy sh.Cells(NextRow, "A")
On Error Resume Next
col.Add .Cells(i, COL_TEST_NAME).Value, .Cells(i, COL_TEST_NAME).Value
On Error GoTo 0
End If
Next i

LastRow = NextRow
NextRow = NextRow + 2
For Each itm In col

NextRow = NextRow + 1
sh.Cells(NextRow, "D").Value = itm
sh.Cells(NextRow, "F").Value = _
"=COUNTIF(" & COL_TEST_NAME & "1:" & COL_TEST_NAME & LastRow & _
"," & COL_TEST_NAME & NextRow & ")"
Next itm
End With

End Sub
[/vba]

[Hoopsah]

Hi XLD,

Firstly, can I apologise for messing you about, I genuinely thought that I had just copied a portion of the report when I first posted this - I now realise this was not the case, so please let me apologise - SORRY MATE.

Secondly - this new module works fantastically, I cannot thank you enough - I hate to say but it would have taken me the best part of the next 2 months to get this done the way you have.

Thanks again!!

[Bob Phillips]

Apologies not needed mate. Just trying to help you to help us. for future posts.

And it feels like it has taken me 2 months :-)