Cell Block
03-25-2010, 08:55 AM
Hi all :hi: ,
I have a currently working macro for an employee list workbook that I need to alter for a newer version of the same. Currently, there are two worksheets, Active & Inactive. Column headings are in row 2, starting with column A and ending at column I, Employment Status, Name, Hire Date, etc. I use data validation in column A, with an in-cell drop box for employment status of each employee - you guessed it - Active or Inactive. When the status of an employee is changed from Active to Inactive, the row with that employee's data is copied from the Active sheet, pasted after the last row of data on the Inactive sheet.
Here is the macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lgRow As Long
Dim strStatusColumn As String
strStatusColumn = "A" 'change to the column where Status is enterd
If Intersect(Target, Range(strStatusColumn & ":" & strStatusColumn)) Is Nothing Then Exit Sub
If Range(strStatusColumn & Target.Row).Value = "No" Then
lgRow = Target.Row
Range(Cells(lgRow, 1), Cells(lgRow, Columns.Count)).Cut _
Destination:=Sheets("Inactive").Cells(Sheets("Inactive").Rows.Count, 1).End(xlUp).Offset(1, 0)
Rows(lgRow).Delete Shift:=xlUp
End If
End Sub
Request #1
I am now using a newer version of this workbook, with its purpose similar, but format and layout totally different. In the new one, Employee names are by columns, starting with column F until AS, with the data validation now as a row - row 3 - starting with F until AS. I'm looking for similar functionality, i.e. I select Inactive status and the column is copied, then inserted as column F in the sheet "Inactive", the older columns moving to the right as new columns are pasted in. Once inserted in Inactive, the column for that employee is deleted in the original sheet, Active.
Request #2, related to #1
Also, if possible, I would like a safety feature added, a popup that says "You are about to remove "Employee Name" as Inactive, are you sure you want to continue?" YES/NO? Employee names are in row 2, columns F until AS.
As I am learning VBA, please explain your code in the following steps to help me learn:
:help 1. The new code for moving/copying/inserting/pasting the column based on my data validation drop box. Best practice to move/paste, or rather copy/insert/delete instead? I figured that copy/insert seemed safe, then delete after successful insertion, but maybe both are the same. Please annotate what you did to my code to change the process over to the columns from my original macro which moved rows.
:help 2. Please explain the msg box in a separate example so that I can keep the two macro snippets apart and understand more easily how they work. If you like you can post the entire code as a final example.
Thanks in advance, I have been toying with this for a week and just don't understand enough about VBA to make this code work in my new workbook. :banghead:
I have a currently working macro for an employee list workbook that I need to alter for a newer version of the same. Currently, there are two worksheets, Active & Inactive. Column headings are in row 2, starting with column A and ending at column I, Employment Status, Name, Hire Date, etc. I use data validation in column A, with an in-cell drop box for employment status of each employee - you guessed it - Active or Inactive. When the status of an employee is changed from Active to Inactive, the row with that employee's data is copied from the Active sheet, pasted after the last row of data on the Inactive sheet.
Here is the macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lgRow As Long
Dim strStatusColumn As String
strStatusColumn = "A" 'change to the column where Status is enterd
If Intersect(Target, Range(strStatusColumn & ":" & strStatusColumn)) Is Nothing Then Exit Sub
If Range(strStatusColumn & Target.Row).Value = "No" Then
lgRow = Target.Row
Range(Cells(lgRow, 1), Cells(lgRow, Columns.Count)).Cut _
Destination:=Sheets("Inactive").Cells(Sheets("Inactive").Rows.Count, 1).End(xlUp).Offset(1, 0)
Rows(lgRow).Delete Shift:=xlUp
End If
End Sub
Request #1
I am now using a newer version of this workbook, with its purpose similar, but format and layout totally different. In the new one, Employee names are by columns, starting with column F until AS, with the data validation now as a row - row 3 - starting with F until AS. I'm looking for similar functionality, i.e. I select Inactive status and the column is copied, then inserted as column F in the sheet "Inactive", the older columns moving to the right as new columns are pasted in. Once inserted in Inactive, the column for that employee is deleted in the original sheet, Active.
Request #2, related to #1
Also, if possible, I would like a safety feature added, a popup that says "You are about to remove "Employee Name" as Inactive, are you sure you want to continue?" YES/NO? Employee names are in row 2, columns F until AS.
As I am learning VBA, please explain your code in the following steps to help me learn:
:help 1. The new code for moving/copying/inserting/pasting the column based on my data validation drop box. Best practice to move/paste, or rather copy/insert/delete instead? I figured that copy/insert seemed safe, then delete after successful insertion, but maybe both are the same. Please annotate what you did to my code to change the process over to the columns from my original macro which moved rows.
:help 2. Please explain the msg box in a separate example so that I can keep the two macro snippets apart and understand more easily how they work. If you like you can post the entire code as a final example.
Thanks in advance, I have been toying with this for a week and just don't understand enough about VBA to make this code work in my new workbook. :banghead: