How to copy data based on criteria metSolved/Closed

Question

Help!

I am totally stuck and this is probably a very simple fix.  I have two sheets "Priority Accounts" and "ACN Dashboard".  I need to move data in one column over from 'Priority Accounts" to "ACN Dashboard' if data in column '6' equals x.  I have tried auto filter and a few other things but am not getting what I need.  I want this to be an automatic thing since this is a live workbook where column 6 in "priority accounts" will be constantly updated.  Please help!

Sam

TrowaD · Answer

Hi Sam,

No worries man, a macro can be written for this.

I just want to make sure we are not confusing columns with rows here.
Can you confirm that either the data being moved is located in a row or that the x's are located in a row.

One more thing. You want this to be automatic. What did you have in mind:
1) when x is entered, move data. 
2) run the code at the end of the day to make sure all data is entered and then remove the x's so they aren't moved twice.
3) run the code at the end of the day to make sure all data is entered and then remove all data being moved.

If you choose for option 1 then please specify move as cut/paste or copy/paste.

Please note that the more column/row references you provide the more accurate the code will be.


Best regards,
Trowa

katcrisjul · Answer

Thanks for responding Trowa!

Okay so the data being moved is in a row but I only want the information from one cell moved.  Example; if Northside Hospital, which is in column A has an "x" in column 6, I want the name "Northside Hospital" to copy to my Sheet 2, which has a running dashboard of all companies that we've engaged with.  Sheet 1 is an actua live working document that directors update daily/hourly even so currently there are 300 names in there.  Over the next few months, they'll be engaging with every one of them; I want my dashboard on sheet 2 to always have the most up-to-date list of companies engaged.

As far as what option I'd want, I'd want something similar to your option 1; when x is entered, copy data to sheet 2.  I know this might complicate things since x's entered yesterday should be excluded from the ones entered today.

Let me know if I need to further clarify.

Thanks.

TrowaD · Answer

OK Sam, that is more clear.

One thing you didn't mention is the destination of the copied value on sheet 2. But let me provide a code so you can see how it works. 

The code below will place the copied value in the first available cell in column A.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Value = "x" Then
    Target.Offset(0, -5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub

Now enter an "x" in column F of sheet 1 and notice that the corresponding value in column A is copied to column A of sheet 2.

Is this how you foresaw it?

Best regards,
Trowa

katcrisjul · Answer

Hi Trowa, this is exactly how I foresaw it.  I have a few questions though (and keep in mind, i'm a bit of a beginner so might get some words jumbled).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Value = "x" Then
    Target.Offset(0, -5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub

My questions are as follows:

Where do I see that whatever is in column A is going to be copied over to Sheet 2?

I have titles to my columns and sheets so maybe this can help not only clarify but teach me at the same time:

Worksheet 1 (where the info is captured) is "priority Accounts"
Column F is "IPS Campaign Engagement"
Column A is "Target Name"

Destinatin: 
Sheet 2 is ACN Dashboard
Start at Column B
Row 42 and every row thereafter as needed

Another question: how do I assign it to a button macro once my code is in?  The workbook will be updated several times a day so i'm assuming assigning it to a macro would be the best right?

TrowaD · Answer

Hi Sam,

Your first question is unclear to me. Do you want a message box to appear letting you know which value is copied over? Or are you wandering how the code works?

Target.Offset(0, -5).Copy
This refers to the value being copied.
Target refers to the value being changed, which is a value in column F.
Offset(0,-5) means 5 columns to the left of column F. 

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 
This refers to the destination of the copied value. It consists of a sheet reference and a range reference. The range reference looks at the maximum numbers of rows in column A, then moves the cursor up to the first available value and to not overwrite that value we use Offset to move the cursor down a row.

On to your second question.
The macro is setup to run automatically, do you want to run it manually using a shortcut or button?

Best regards,
Trowa

tiakeew · Answer

Hi Trowa,

I've been reading this thread for a while and I am not too sure how this works.

The file i'm trying to work can be found here:
https://drive.google.com/file/d/0B8e2lEDjU_14Mkt1VFZaQWg5UlU/edit?usp=sharing

I've a similar request to the TS, trying to import data to Sheet1 from DB using parameters from Column Y "Status".

If status is WIP then I want to populate Column B to L in Sheet1 from DB using specific columns. I've given the exact columns I want to populate Sheet1 with below:

Sheet1 = DB
Column B = Column C
Column C = Column E
Column D = Column F
Column E = Column K
Column F = Column L
Column G = Column M
Column H = Column N
Column I = Column O
Column J = Column P
Column K = Column Q
Column L = Column AC

Will appreciate your help as I've been trying to solve this using formulas for the longest time.

TrowaD · Answer

Hi Tiakeew,

See if the code below is to your liking.

The code will run automatically when "WIP" is entered in Column Y of the "DB" sheet, after implementing of course.

To implement, right-click on the "DB" sheet tab and select "View code". Paste the code in the big white field that appears.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long

If Intersect(Target, Range("Y:Y")) Is Nothing Then Exit Sub

If Target = "WIP" Then
    lRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Sheets("Sheet1").Range("B" & lRow) = Range("C" & Target.Row)
    Sheets("Sheet1").Range("C" & lRow) = Range("E" & Target.Row)
    Sheets("Sheet1").Range("D" & lRow) = Range("F" & Target.Row)
    Sheets("Sheet1").Range("E" & lRow) = Range("K" & Target.Row)
    Sheets("Sheet1").Range("F" & lRow) = Range("L" & Target.Row)
    Sheets("Sheet1").Range("G" & lRow) = Range("M" & Target.Row)
    Sheets("Sheet1").Range("H" & lRow) = Range("N" & Target.Row)
    Sheets("Sheet1").Range("I" & lRow) = Range("O" & Target.Row)
    Sheets("Sheet1").Range("J" & lRow) = Range("P" & Target.Row)
    Sheets("Sheet1").Range("K" & lRow) = Range("Q" & Target.Row)
    Sheets("Sheet1").Range("L" & lRow) = Range("AC" & Target.Row)
End If

End Sub

Best regards,
Trowa

tiakeew · Answer

Hi Trowa,

I've tried the Macro and I realized I forgot to mention that the data in the DB" sheet is exported (copied and pasted) so "WIP" is not manually entered into Column Y under the "DB" sheet.

How then can I adjust the code such that it will still auto populate the fields in Sheet1?

In addition, will it be possible to do a search of Column AD as well for a specific word? E.g. Deskjet 2540.

Therefore the code will only do an auto populate of Sheet1 if the data in Column Y and Column AD of "DB" sheet fulfills both criteria.

And all these bearing in mind that the database is copied and pasted over into DB and not manually entered.

Thanks for your help!

tiakeew · Answer

Hi Trowa,

How can I edit the code to copy the values from the Worksheet "DB" to another Workbook?

Been looking at this code here:

Sub mySales()

Dim LastRow As Integer, I As Integer, erow As Integer

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For I = 2 To LastRow

If Cells(i, 1) = Date And Cells(i, 2) = "Sales" Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy

Workbooks.Open Filename:="C:\Users	akyar\Documents\salesmaster-new.xlsx"
Worksheets("Sheet1?).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i
End Sub

But it does not fit what I want in  terms of copying the data from selected columns.
Would appreciate if you could help!!

Thank you

TrowaD · Answer

Hi Tiakeew,

Here is my code with adjustments.

Put this code under "Sheet1" of the "salesmaster-new" workbook.

Make sure to save your "Tiakeew - DO list Template (Printer)" workbook before selecting "Sheet1" of the "salesmaster-new" workbook or else Excel will ask you if you want to re-open the file without any adjustments being saved.

If the worksheet and/or workbook references above doesn't match yours, then replace them with what you have in the code (best done by using find/replace [Ctrl+ H]).

Private Sub Worksheet_Activate()
Dim lRow, lRowDB As Long
Dim DestBook As Workbook

Set DestBook = Workbooks("salesmaster-new")

Range("B2:L" & Rows.Count).ClearContents

Workbooks.Open Filename:="C:\Users	akyar\Documents\Tiakeew - DO list Template (Printer).xlsx"
Sheets("DB").Select

lRowDB = Sheets("DB").Range("Y" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("DB").Range("Y70:Y" & lRowDB)
    If cell = "WIP" And cell.Offset(0, 5) = "Deskjet 2540" Then
        lRow = DestBook.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
        DestBook.Sheets("Sheet1").Range("B" & lRow) = Sheets("DB").Range("C" & cell.Row)
        DestBook.Sheets("Sheet1").Range("C" & lRow) = Sheets("DB").Range("E" & cell.Row)
        DestBook.Sheets("Sheet1").Range("D" & lRow) = Sheets("DB").Range("F" & cell.Row)
        DestBook.Sheets("Sheet1").Range("E" & lRow) = Sheets("DB").Range("K" & cell.Row)
        DestBook.Sheets("Sheet1").Range("F" & lRow) = Sheets("DB").Range("L" & cell.Row)
        DestBook.Sheets("Sheet1").Range("G" & lRow) = Sheets("DB").Range("M" & cell.Row)
        DestBook.Sheets("Sheet1").Range("H" & lRow) = Sheets("DB").Range("N" & cell.Row)
        DestBook.Sheets("Sheet1").Range("I" & lRow) = Sheets("DB").Range("O" & cell.Row)
        DestBook.Sheets("Sheet1").Range("J" & lRow) = Sheets("DB").Range("P" & cell.Row)
        DestBook.Sheets("Sheet1").Range("K" & lRow) = Sheets("DB").Range("Q" & cell.Row)
        DestBook.Sheets("Sheet1").Range("L" & lRow) = Sheets("DB").Range("AC" & cell.Row)
    End If
Next cell

End Sub 

Best regards,
Trowa

How to copy data based on criteria met

10 replies

Subscribe To Our Newsletter!