I have the following worksheet change code. My question to the forum is what is the best practice for identifying target range? The below code runs on a worksheet that has 12K lines and growing. To manage this code most efficiently as it grows and changes, is it best to use named ranges for the target range or simply identifying the ranges (ie rngcheck)? I am open to changing code if necessary. It may be that there is no significant advantage either way. I am still learning VBA and would appreciate any advice. Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim Answer As Long
Dim Lrow1 As Long
Dim rngCheck As Range
Dim CheckCell As Range
Dim Lrow As Long
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Sheets("TEST")
Set WS2 = Sheets("TEST2")
Set rngCheck = Intersect(Target, Range("C3:C5, C7:C1234, C1236:C12000"))
If Not rngCheck Is Nothing Then
For Each CheckCell In rngCheck.Cells
If CheckCell.Value > 0 Then
With WS1.UsedRange
Lrow = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
WS2.Cells(CheckCell.Row, "C").Copy
WS1.Cells(Lrow, "D").PasteSpecial xlPasteValues
WS2.Cells(CheckCell.Row, "B").Copy
WS1.Cells(Lrow, "K").PasteSpecial xlPasteValues
WS2.Cells(CheckCell.Row, "E").Copy
WS1.Cells(Lrow, "J").PasteSpecial xlPasteValues
WS2.Cells(CheckCell.Row, "D").Copy
WS1.Cells(Lrow, "E").PasteSpecial xlPasteValues
End With
End If
Next CheckCell
End If
Set rng1Check = Intersect(Target, Range("C2,C6"))
If Not rng1Check Is Nothing Then
For Each Check1Cell In rng1Check.Cells
If rng1Check.Value > 0 Then
Answer = MsgBox("Add Assembly Kit Yes or No.", vbYesNo)
Lrow1 = WS1.Cells(WS1.Rows.Count, "D").End(xlUp).Row + 1
Select Case Answer
Case vbYes
WS2.Cells(Check1Cell.Row, "C").Copy
WS1.Cells(Lrow1, "D").PasteSpecial xlPasteValues
WS2.Cells(Check1Cell.Row, "B").Copy
WS1.Cells(Lrow1, "K").Value = Target.Offset(0, -1).Value & " " & "w/Assembly Kit"
WS2.Cells(Check1Cell.Row, "D").Copy
WS1.Cells(Lrow1, "E").PasteSpecial xlPasteValues
WS1.Cells(Lrow1, "J").Value = Target.Offset(0, 2).Value + 10
Case vbNo
WS2.Cells(Check1Cell.Row, "C").Copy
WS1.Cells(Lrow1, "D").PasteSpecial xlPasteValues
WS2.Cells(Check1Cell.Row, "B").Copy
WS1.Cells(Lrow1, "K").PasteSpecial xlPasteValues
WS2.Cells(Check1Cell.Row, "E").Copy
WS1.Cells(Lrow1, "J").PasteSpecial xlPasteValues
WS2.Cells(Check1Cell.Row, "D").Copy
WS1.Cells(Lrow1, "E").PasteSpecial xlPasteValues
End Select
End If
Next Check1Cell
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Bookmarks