July Unit Charges.xlsm
Originally Posted by
volvodriver
Ok, haven't uploaded a file here before but here goes.
Hi
Why not just have a single sheet for the Individual Apartment detail, and use Advanced Filter to extract the data just relating to that apartment.
In the attached workbook, I have set up some Dynamic ranges, and then used event code on the Sheet called Apartment, so that when you choose a different apartment number from the dropdown list in cell C2, a macro is run which extracts the data relating to that department and puts in the totals.
named ranges
Apartments =Lists!$A$1:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))
myCrit =Apartment!$C$1:$C$2
mydata ='All units'!$A$3:INDEX('All units'!$L:$L,COUNTA('All units'!$A:$A)+2)
myDest =Apartment!$A$6:$L$6
Event code on sheet Apartment
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count > 1 Then Exit Sub
If Target <> Range("C2") Then Exit Sub
Application.EnableEvents = False
FilterData
Application.EnableEvents = True
End Sub
Advanced filter Code
Sub FilterData()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lr As Long
Set ws1 = Sheets("All Units")
Set ws2 = Sheets("Apartment")
ws1.Range("myData").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ws2.Range("myCrit"), CopyToRange:=ws2.Range("myDest"), Unique:=False
lr = ws2.Cells(Rows.count, 1).End(xlUp).Row + 2
Cells(lr, "G") = "Apartment Total"
Cells(lr, "H").FormulaR1C1 = "=SUM(R7C:R[-1]C)"
Cells(lr, "J").FormulaR1C1 = "=SUM(R7C:R[-1]C)"
Cells(lr, "K").FormulaR1C1 = "=SUM(R7C:R[-1]C)"
Cells(lr, "L").FormulaR1C1 = "=SUM(R7C:R[-1]C)"
Range(Cells(lr, "G"), Cells(lr, "L")).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Application.CutCopyMode = False
End Sub
Bookmarks