Prev: Use 'like' or regex to match number-letter pattern substrings
Next: Active X Controls Shrinking on Excel worksheet
From: Ayo on 2 Jun 2010 15:40
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

 | 
Pages: 1
Prev: Use 'like' or regex to match number-letter pattern substrings
Next: Active X Controls Shrinking on Excel worksheet