A
Ayo
Here is the code:
lRow = Worksheets("BO Download").Cells(Rows.Count, "BJ").End(xlUp).Row
Worksheets("BO Download").Range("BJ4:BK" & lRow - 3).Copy
Worksheets("Completions Summary (Vendor)").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
The only other thing is that I have a Worksheet_SelectionChange(ByVal Target
As Range) sub for Worksheets("Completions Summary (Vendor)") as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, rngAll As Range
Dim ws As Worksheet
Dim lRow As Long
lRow = Me.Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Me.Rows("4:" & lRow).EntireRow.Hidden = False
If Me.Range("B5") = "" Then
Else
With ActiveCell
If .Value = "Market" Then
Me.Rows("4:" & lRow - 1).EntireRow.Hidden = False
Me.Range("B" & lRow).Value = "TOTALS"
Call sumALL(Me)
ElseIf Left(.Address, 2) = "$B" Then
If Right(.Address, 1) >= 4 And Right(.Address, 2) <= lRow -
1 Then
Application.EnableEvents = False
Me.Range("B" & lRow).Value = "MARKET TOTALS"
For Each c In Me.Range("B4:B" & lRow - 1).Cells
If c.Value <> .Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden =
True
Call sumREGION(Me, .Value)
Application.EnableEvents = True
End If
End If
End With
End If
Me.Calculate
End Sub
lRow = Worksheets("BO Download").Cells(Rows.Count, "BJ").End(xlUp).Row
Worksheets("BO Download").Range("BJ4:BK" & lRow - 3).Copy
Worksheets("Completions Summary (Vendor)").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
The only other thing is that I have a Worksheet_SelectionChange(ByVal Target
As Range) sub for Worksheets("Completions Summary (Vendor)") as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, rngAll As Range
Dim ws As Worksheet
Dim lRow As Long
lRow = Me.Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Me.Rows("4:" & lRow).EntireRow.Hidden = False
If Me.Range("B5") = "" Then
Else
With ActiveCell
If .Value = "Market" Then
Me.Rows("4:" & lRow - 1).EntireRow.Hidden = False
Me.Range("B" & lRow).Value = "TOTALS"
Call sumALL(Me)
ElseIf Left(.Address, 2) = "$B" Then
If Right(.Address, 1) >= 4 And Right(.Address, 2) <= lRow -
1 Then
Application.EnableEvents = False
Me.Range("B" & lRow).Value = "MARKET TOTALS"
For Each c In Me.Range("B4:B" & lRow - 1).Cells
If c.Value <> .Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden =
True
Call sumREGION(Me, .Value)
Application.EnableEvents = True
End If
End If
End With
End If
Me.Calculate
End Sub