Automatically copying rows with a particular attribute to a different worksheet

B

byronova

New Member
Joined
Oct 23, 2008
Messages
18
Hi. my problem involves displaying rows that meet certain ‘criteria’, on a separate worksheet, in real time. The criteria will be determined by a selection made from a dropdown list in the row in question.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
ILLUSTRATION: Let us say (for example) that I am dealing with a worksheet for MEN’S DATA. Each man’s data will be on a separate row. On each row, one of the cells has a dropdown list having 2 options, SHORT and TALL. I have a separate report worksheet for TALL men. I mean, I want every row for which I select the TALL option, to be copied to my TALL report worksheet. I want the selection of the TALL option to be a kind of trigger that promptly copies the row to the TALL worksheet, once I make the selection from the dropdown list.

Any help I can get will be appreciated.
 
Last edited:
Sort by date Sort by votes
Hello and welcome to MrExcel.

Which column has the validated TALL/SHORT cell?
 
Upvote 0
OK well this assumes that the column is 1 (A) and that the sheet to copy to is TALL. Right click the MEN'S DATA sheet tab, select View Code and paste in

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Const MyColumn As Integer = 1 '<<<<<<<<<<<<<<<<<<<<<<<<< change to correct column number
Dim LR As Long
If Target.Column = MyColumn And Target.Value = "TALL" Then
    With Sheets("TALL")
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
End If
End Sub
 
Upvote 0
Thank you very much for making time to reply.

The column with the validated TALL/SHORT options could be any cell in the particular row. It is one of many data attributes in the MEN table. I however, want to have a TALL report sheet that updates automatically everytime data about and man has TALL selected.

I need the entire row containing the TALL property to be copied to my TALL report sheet. I also want to be able to do this for the SHORT property.
 
Upvote 0
Try

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Value = "TALL" Or Target.Value = "SHORT" Then
    With Sheets(Target.Value)
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        Target.EntireRow.Copy Destination:=.Range("A" & LR + 1)
    End With
End If
End Sub
 
Upvote 0
Hello VoG,

Thanks a mill. This works great! However, I have just 2 more questions;

1) Can I spacify the row in the TALL worksheet from which I want the pasting to start. i.e, the position of the first data row in the TALL worksheet?

2) The workbook I am using contains more than 1 worksheet (e.g. TALL/SHORT men from different states, with each state on a separate worksheet). This means that I might want to write to the TALL worksheet from different worksheets (states). Is it possible to make this some sort of global macro, so that all the necessary worksheets in my workbook can write to the TALL worksheet?

Your help is greatly appreciated.
 
Upvote 0
This should do what you want. First delete the existing code. Then, whilst still in the VB Editor you should see ThisWorkbook listed in the 'Project - VBAProject' window on the left. Right click it, select View Code and paste in

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const FirstRow As Long = 10 'change 10 to the first row to start on
Dim LR As Long
If Target.Count > 1 Then Exit Sub
If Target.Value = "TALL" Or Target.Value = "SHORT" Then
    Application.EnableEvents = False
    With Sheets(Target.Value)
        LR = WorksheetFunction.Max(.Range("A" & Rows.Count).End(xlUp).Row + 1, FirstRow)
        Target.EntireRow.Copy Destination:=.Range("A" & LR)
    End With
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Hello VoG,
Thank you again. the only limitation i am experiencing is that if someone's height (thru reverse-growth or some other means, ha ha) should change from TALL to SHORT, the copied row still remains in the TALL database. Is there any way i can ensure that the row gets deleted once the height status changes, while simultaneaously getting copied to the appropriate report sheet?

Also, I want to be able to list the NAMES ONLY of all TALL men. Lets assume the NAME column is column C.

Thank you for your time.
 
Last edited:
Upvote 0
Is there a unique identifier in a defined position in the row that would enable searching for that in the 'wrong' sheet in order to remove it?

Edit: I see that you modified your post. So instead of the entire row you only want the values in column C. Are the names unique?
 
Upvote 0
OK well this now just copies from column C to column A of TALL or SHORT. If the same value is found in the 'other' sheet it is removed. This will only function as intended if the values copied from column C are unique.

Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const FirstRow As Long = 10 'change 10 to the first row to start on
Dim LR As Long, ToFind As Variant, Found As Range, OtherSheet As String
If Target.Count > 1 Then Exit Sub
If Target.Value = "TALL" Or Target.Value = "SHORT" Then
    Application.EnableEvents = False
    With Sheets(Target.Value)
        LR = WorksheetFunction.Max(.Range("A" & Rows.Count).End(xlUp).Row + 1, FirstRow)
        ToFind = Sh.Range("C" & Target.Row)
        .Range("A" & LR).Value = ToFind
    End With
    If Target.Value = "TALL" Then
        OtherSheet = "SHORT"
    Else
        OtherSheet = "TALL"
    End If
    Set Found = Sheets(OtherSheet).Columns("A").Find(what:=ToFind)
    If Not Found Is Nothing Then Found.EntireRow.Delete
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
685
Alex Blakenburg
A
A
  • Question Question
Assign Rows of Data to Employees from Master
Replies
9
Views
662
mumps
mumps
S
  • Question Question
Office Script to Copy Rows From One Worksheet To Another.
Replies
2
Views
1K
Fluff
Fluff
K
  • Question Question
Copy row to another sheed based on drop-down list
Replies
6
Views
569
mumps
mumps
A
  • Question Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
1K
AndyPandy27
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top