Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim URL As String, strEmail As String, strSubject As String, strBody As String Dim cel As Range, rg As Range, targ As Range, rg2 As Range '*****Brad added declaration of rg to this statement If Sh.Name <> "Sheet1" Then Exit Sub Set targ = Intersect(Target, Range("I:I")) If targ Is Nothing Then Exit Sub For Each cel In targ.Cells If cel.Value = "B" Then Application.EnableEvents = False If ValidateColumnI(cel) Then strEmail = Range("$K" & Right(cel.Address, 2)).Value strSubject = "Job Completed" strBody = "Completion Date" & ": " & cel.EntireRow.Cells(1, "O").Value & vbLf & _ ", Resolution Code" & ": " & cel.EntireRow.Cells(1, "P").Value & vbLf & _ ", Tech #" & ": " & cel.EntireRow.Cells(1, "Q").Value strURL = "mailto:" & strEmail & "?subject=" & strSubject & "&body=" & strBody ShellExecute 0&, vbNullString, strURL, vbNullString, vbNullString, vbNormalFocus With Worksheets("Sheet2") Set rg2 = .UsedRange Set rg2 = rg2.Cells(rg2.Rows.Count + 1, 1).EntireRow cel.EntireRow.Cut rg2 'cel.EntireRow.Delete '*****Brad commented this line out End With Set rg = Sh.UsedRange '*****Brad added this statement rg.Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlYes '*****Brad added this statement Else cel.Value = "" 'Didn't pass validation, so undo the selection of "B" End If Application.EnableEvents = True End If Next End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|how can I determine if an Excel spreadsheet will fit on an 8.5 by 11 inch sheet?||3||59|
|I need to extract city and state from a cell in Excel||6||31|
|Content of a read only textbox are not being saved in table in asp.net - VB||5||36|
|VBA to clear/put no-fill the cells background color for all sheets of active workbook||5||35|
Join the community of 500,000 technology professionals and ask your questions.