Excel/Help VBA

Advertisement


Question
QUESTION: Hello,

I am trying to copy a row of information from one sheet to another, then delete the empty row.

My sheets named "Active", "Placements","OnHold", and "Withdrawn". On the "Active" sheet if column V (which has a drop down list containing the other sheet names) has "Placements" selected i would like the information to be moved to the placement sheet. If "withdrawn" is selected, i would like in info moved to the withdrawn sheet.
I have a code started but its not working.
please help!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim trgrow
If Target.Column <> 22 Then Exit Sub
Application.EnableEvents = False
If LCase(Target.Value) = "Placements" Then
With Sheets("Placements")
If LCase(Target.Value) = "OnHold" Then
With Sheets("OnHold")
If LCase(Target.Value) = "Withdrawn" Then
With Sheets("Withdrawn")
trgrow = Target.Row
lr = .Cells(Rows.Count, 22).End(xlUp).Row + 1
Target.EntireRow.Cut Destination:=.Cells(lr, 1)
End With
   Rows(trgrow).Select
   Selection.Delete Shift:=xlUp

Cells(trgrow, 22).Select
End If
Application.EnableEvents = True
End Sub


Also, my info starts on row 3 becuase i have a header.
Thanks!!

ANSWER: NIcole,

I thought I had changed the W in withdraw to a lowercase, but apparently not. It is changed below in this revision to my answer.

I would see it something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim trgrow, sh as worksheet

If Target.Column <> 22 Then Exit Sub
If Target.count > 1 then exit sub
If Target.row < 3 then exit sub
If LCase(Target.Value) = "placements" Then
set sh = WorkSheets("Placements")
ElseIf LCase(Target.Value) = "onhold" Then
set sh = worksheets("OnHold")
ElseIf LCase(Target.Value) = "withdrawn" Then
set sh = WorkSheets("Withdrawn")
end if
if not sh is nothing then
 Application.EnableEvents = False
  trgrow = Target.Row
  lr = sh.Cells(sh.Rows.Count, 22).End(xlUp).Row + 1
  Target.EntireRow.Cut Destination:=sh.Cells(lr, 1)
  me.Rows(trgrow).EntireRow.Delete  ' me refers to the sheet with the event code
  me.Cells(trgrow, 22).Select
 Application.EnableEvents = True
End If
End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hello,
Thank you for your help!
When the row is placed on the new sheet, it is put on row 1. Is there a way that it can be filtered so it will be in alpha order by name? Which is Column B.
Thank you

Answer
NIcole,

this assumes that the three sheets you copy to are either blank or the data already in the sheets start in row 1 and are sorted ascending on column B


It will search down through the existing data in the appropriate sheet in column B and insert a row where the first name is greater than the name in the row that triggered the macro in the sheet begin cut from.   It will then cut the row to the newly inserted row.  If it doesn't find a name that is greater than the name in the source sheet, it will cut that row to the row at the end of the data in the destination sheet.

So in my tests, it does what you asked (place the moved data into it proper place in a sorted list of data).


Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim trgrow, sh As Worksheet
Dim sName As String, sName1 As String
Dim lr As Long, i As Long
If Target.Column <> 22 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
If LCase(Target.Value) = "placements" Then
Set sh = Worksheets("Placements")
ElseIf LCase(Target.Value) = "onhold" Then
Set sh = Worksheets("OnHold")
ElseIf LCase(Target.Value) = "withdrawn" Then
Set sh = Worksheets("Withdrawn")
End If
If Not sh Is Nothing Then
 Application.EnableEvents = False
  trgrow = Target.Row
  sName = LCase(Me.Cells(trgrow, 2))
  lr = sh.Cells(sh.Rows.Count, 22).End(xlUp).Row + 1
  For i = 1 To lr
    sName1 = LCase(sh.Cells(i, "B").Value)
    If sName < sName1 Or Len(Trim(sName1)) = 0 Then
      sh.Rows(i).EntireRow.Insert Shift:=xlShiftDown
      Target.EntireRow.Cut Destination:=sh.Cells(i, 1)
      Me.Rows(trgrow).EntireRow.Delete  ' me refers to the sheet with the event code
      Me.Cells(trgrow, 22).Select
      Exit For
    End If
  Next
 Application.EnableEvents = True
End If
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.