LosingMyMindAgain
New Member
- Joined
- Sep 22, 2014
- Messages
- 11
Hello. I am fairly new to Excel macros and apologize ahead of time if this is a stupid question. I am looking to work with two sheets of a workbook. I found a macro that does what I want, but only when dealing with one sheet. Sheet1 is the data entry portion and Sheet2 is where the totals will be. With this macro I am hoping to cycle through Sheet1 Column N to distinguish the unique counties listed and then count the occurrences of each county. I'd then like to paste the unique counties on Sheet2 starting at E3. Then also paste the counts on Sheet2 starting at F3. How do I go about tweaking this so I can reference back and forth between the two sheets? Any tips would be greatly appreciated. Thank you!
Sub Special_Countif()
Dim i, LastRowA, LastRowB
'A=Data Column B=Unique Names C=Count
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.EnableEvents = False
Columns("B:C").ClearContents
For i = 1 To LastRowA
If Application.CountIf(Range("B:B"), Cells(i, "A")) = 0 Then
Cells(i, "B").Offset(1, 0).Value = Cells(i, "A").Value
End If
Next
Columns("B:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LastRowB
Cells(i, "C").Value = Application.CountIf(Range("A:A"), Cells(i, "B"))
Next i
Range("B1").Value = "Entry"
Range("C1").Value = "Occurrences"
Range("B1:C1").HorizontalAlignment = xlCenter
Range("B1").Select
Columns("B:C").AutoFit
Application.EnableEvents = True
End Sub