Conditional Copy a Row

Microsoft Excel 2007
January 27, 2010 at 01:20:29
Specs: Windows XP
Please help!

In Microsoft Excel 2007, I need to copy a row from one worksheet (MasterList) to other worksheets (OnRollList) and (LeaverList) in such a way that whenever I change the value of a column in MasterList (DOL) the other two sheets should be updated accordingly. By default (DOL) in the worksheet (MasterList) would be blank, in this case all rows having blank DOL should be copy into the worksheet “OnRollList”, in the other case all the rows having DOL should be copy into the worksheet “LeaverList”. I want to copy that row to the OnRollList and LeaverList list as the next row in the relevant sheet.

Keep in mind I have beginner level knowledge about the Excel and have no idea about the macros

I appreciate your help!
Thanks.

edited by moderator: Post moved from Windows XP Forum


See More: Conditional Copy a Row

Report •


#1
January 27, 2010 at 11:57:30
I need a little more of an explanation...

re: "whenever I change the value of a column"...

I assume you mean "whenever I change the value of any cell in a specific column"...

re: "By default (DOL) in the worksheet (MasterList) would be blank, in this case all rows having blank DOL should be copy into the worksheet “OnRollList"

This is a tad confusing to me. If by default the cells in this column are blank, then wouldn't all of the rows be copied to "OnRollList"?



Report •

#2
January 29, 2010 at 05:12:12
Here is the more explanation:

In the MasterLIst sheet, following would be the columns:
Name, Designation, Department, Sex, DOJ(Joining Date) , DOL (Leaving Date)

Whenever new employee comes in, his/her information would be entered under the relevant columns except the DOL. When an employee leaves the organization then his/her date of leaving would be entered under DOL column.

I am willing to copy all the employee’s inform into the “OnRollList” sheet, if the “DOL” is blank and if the “DOL” is not blank, copy all the employees inform into the “LeaverList” sheet.

If formula can’t be apply to check the blank cell value of a row, we can add a new column as “OnRoll (Y/N)” and base on the value of “Y/N” can we get the desired results.
Presently by putting the formula, I can copy the desired records in the relevant sheets but the problem is: record are copied on the same row number as on the row number of MsterList . In this if the formula is not true; there are blanks rows in the desired sheets (OnRollList and LeverList).

Looking forward for the solution

I appreciate your help!
Thanks.


Report •

#3
January 29, 2010 at 06:22:48
Hi,

Here is some code that tests a column to see if the cell is blank or not.
If the cell in that column is blank it transfers the whole row of data to a sheet named Current
If the cell is not blank (presumably a date), it transfers the whole row to a sheet named Left

The rows of data in the Current and Left sheets are continuous - no empty rows.

You will need to change the column offset to match the column with the leaving dates and change sheet names to match.

Attach the code to a command button:
On the main 'Staff' Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Update' or something else meaningful.
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit
Private Sub CommandButton1_Click()
Dim rngLast As Range
Dim rngCell As Range
Dim intRowsOffst As Integer

On Error GoTo ErrHnd

'get current range of data on Sheet named Staff
Set rngLast = Worksheets("Staff").Range("A65534").End(xlUp)

'set destination rows offset = 1 (i.e., after a header row)
intRowsOffst = 1

'select current staff
'loop through each row in staff list
For Each rngCell In Worksheets("Staff").Range("A2:" & rngLast.Address)
    'if leaving date is in Column F
    'change offset column from 5 as required)
    If rngCell.Offset(0, 5).Value = "" Then
        'leaving date is blank
        'select whole row
        rngCell.EntireRow.Copy _
            Destination:=Worksheets("Current").Range("A1").Offset(intRowsOffst, 0)
        'increment row offset
        intRowsOffst = intRowsOffst + 1
    End If
Next rngCell

'reset destination rows offset = 1 (i.e., after a header row)
intRowsOffst = 1

'select leavers
'loop through each row in staff list
For Each rngCell In Worksheets("Staff").Range("A2:" & rngLast.Address)
    'if leaving date is in Column F
    '(change offset column from 5 as required)
    If rngCell.Offset(0, 5).Value <> "" Then
        'leaving date is not blank
        'select whole row
        rngCell.EntireRow.Copy _
            Destination:=Worksheets("Left").Range("A1").Offset(intRowsOffst, 0)
        'increment row offset
        intRowsOffst = intRowsOffst + 1
    End If
Next rngCell
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

Click the 'Update' button.

Note that all cells in column A in the main Staff Worksheet below the last entry must be blank - this is how the code works out the rows that contain data.
On all sheets row 1 is assumed to be a header row. The code can be changed to allow more header rows:
Change A2 in this code (x2) for the main Staff worksheet

Worksheets("Staff").Range("A2:" & rngLast.Address)

For the destination sheets change the initial offset value for each sheet:
intRowsOffst = 1

Hope this gives you a starting point for your solution

Regards


Report •

Related Solutions

#4
March 1, 2010 at 05:28:08
Hi,

The provided solution perfectly solved my problem. Thanks

Sorry for the late reply, I was on Leave and tried today.


Once again bundle of thanks.


Report •

#5
March 1, 2010 at 06:11:36
You're Welcome

Regards

Humar


Report •

Ask Question