Automatically Copy Certain Columns of a Row to Separate Worksheet, Based on Specific Criteria

[mcelmurrygal]

Hi Everyone,

Before I begin, please keep in mind that I am very new to anything about VBA or codes - so please "dummy" it up for me when responding. I have looked at SEVERAL posts and tried a few codes suggested throughout them, but to no avail or success - so now I'm asking for specific help.

I have a workbook where we manually enter data into the main worksheet titled, "Data 2016". Column "G" in this worksheet has a drop-down for us to select a certain option to categorize the event for that row (i.e. - "Win", "Loss", "In Progress", etc.).

In the "Data 2016" worksheet - If one of us changes the selection in Column G to either "W - Won" or "L - Loss"; then the data from Columns A:G in that row is copied over to the next blank row on worksheet titled, "Win-Loss Data".

Hope I'm explaining well - could definitely use some help.
Thanks in advance!

 

[My Aswer Is This]

So if the user selects Loss then that row of data should be copied to sheet named "L-Loss"
So what happens if the user selects "In Progress"

It would be easier if your sheets were given the same name as the value in the drop down.
For example select loss then that row is copied to sheet named "Loss"

 

[mcelmurrygal]

Hi,

So - if the user selects "In Progress" - I don't want anything to happen because the only data that needs to be copied over are the events that have a status update of "Win" or "Loss"

If it's easier, I can create 2 separate reporting worksheets (vs. having the single one titled, "Win-Loss"):
Win Debriefs
Loss Debriefs

Would that help?

 

[FDibbins]

Here is a formula/ARRAY way to do this...
Assuming your data looks something like this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Blah 1[/td][td]Blah 2[/td][td]Blah 3[/td][td]Blah 4[/td][td]Blah 5[/td][td]Blah 6[/td][td]Blah 7[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

1​

[/td][td]

10​

[/td][td]

100​

[/td][td]

1000​

[/td][td]

10000​

[/td][td]win[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td]

2​

[/td][td]

20​

[/td][td]

200​

[/td][td]

2000​

[/td][td]

20000​

[/td][td]loss[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td]

3​

[/td][td]

30​

[/td][td]

300​

[/td][td]

3000​

[/td][td]

30000​

[/td][td]win[/td][/tr]

[tr][td]

5​

[/td][td]dd[/td][td]

4​

[/td][td]

40​

[/td][td]

400​

[/td][td]

4000​

[/td][td]

40000​

[/td][td]loss[/td][/tr]
[/table]
For the extract on the next sheet...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Blah 1[/td][td]Blah 2[/td][td]Blah 3[/td][td]Blah 4[/td][td]Blah 5[/td][td]Blah 6[/td][td]Blah 7[/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td]

1​

[/td][td]

10​

[/td][td]

100​

[/td][td]

1000​

[/td][td]

10000​

[/td][td]win[/td][/tr]

[tr][td]

3​

[/td][td]cc[/td][td]

3​

[/td][td]

30​

[/td][td]

300​

[/td][td]

3000​

[/td][td]

30000​

[/td][td]win[/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
A2=IFERROR(INDEX(Sheet4!A:A,SMALL(IF(Sheet4!$G$2:$G$5="win",ROW(Sheet4!$A$2:$A$5)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
Then copy down and across as needed

 

[My Aswer Is This]

It's OK:
Your quote:

In the "Data 2016" worksheet - If one of us changes the selection in Column G to either "W - Won" or "L - Loss"; then the data from Columns A:G in that row is copied over to the next blank row on worksheet titled, "Win-Loss Data".

So if either:
"W - Won" or "L - Loss"; then the data from Columns A:G in that row is copied over to the next blank row on worksheet titled, "Win-Loss Data".

That's what you want correct?

I can do that.

 

[mcelmurrygal]

@My Aswer Is This,

Yes please - that's exactly what I need - Thank you

 

[FDibbins]

Did you see my post #4?

 

[My Aswer Is This]

Try this:

Now you realize the values must be exactly: "W - Won" or "L - Loss"
The spaces are important.

Your Workbook must be Macro Enabled.
To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Dim Lastrow As Long
Lastrow = Sheets("Win-Loss Data").Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Target.Value = "W - Won" Or Target.Value = "L - Loss" Then
        Range("A" & Target.Row & ":G" & Target.Row).Copy Destination:=Sheets("Win-Loss Data").Rows(Lastrow)
End If
End If
End Sub

 

[mcelmurrygal]

@My Aswer Is This,

Thank you so much!! It works perfectly and this is exactly what I needed!

@FDibbins - thank you for offering your alternate suggestion. I really wanted to go with the code route on this.

Thanks again to you both!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

@My Aswer Is This,

Thank you so much!! It works perfectly and this is exactly what I needed!

@FDibbins - thank you for offering your alternate suggestion. I really wanted to go with the code route on this.

Thanks again to you both!