Conditional Copying and Pasting of a Row

[Madraykin]

Hi All,

Presume this will be VBA-based - I have the below table (sensitive data, so I've dummied some data into my table structure)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Digit[/TD]
[TD]Type[/TD]
[TD]Population[/TD]
[TD]Channel[/TD]
[TD]Message 1[/TD]
[TD]Message 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Red[/TD]
[TD]0[/TD]
[TD]STOP[/TD]
[TD]STOP[/TD]
[TD]STOP[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Blue[/TD]
[TD]1000[/TD]
[TD]Online[/TD]
[TD]Hi[/TD]
[TD]Goodbye[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Green[/TD]
[TD]5000[/TD]
[TD]Online[/TD]
[TD]Hello[/TD]
[TD]See ya[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Black[/TD]
[TD]0[/TD]
[TD]STOP[/TD]
[TD]STOP[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yellow[/TD]
[TD]10000[/TD]
[TD]Online[/TD]
[TD]Good Morning[/TD]
[TD]Adios[/TD]
[/TR]
</tbody>[/TABLE]

Basically I am automating a huge string of processes into my spreadsheet that will action when I choose a value from a drop-down box in sheet 'Step 1' - so far I have achieved all of this by formulae alone, but what I need to do now is create a definitive list that only shows the rows with a population greater than 0, and which don't read 'STOP' in the cells (all of the 0 population rows will read STOP - anything with a population above 0 will contain data)

So the above is in a sheet called 'Step 3' - I need something that will look at the above table, select rows 2,3 and 5 (ie. those with populations greater than 0) and then paste them below the table in a new list (this will start at cell A91 on sheet 'Step 3')

I'm still a VBA beginner in terms of writing my own code but am getting better at understanding what code actually does, and how to adapt it

If this process could be something that triggers automatically when the source cells are populated (at the moment they are blank until I choose my variable from the dropdown on Step 1 sheet) that would be even better!

Any help appreciated - I really learn something new from you guys each time I post on here!

Thanks in advance

Mads

PS if needed my original data starts in Row 59 (Columns A-F) and finishes at Row 83 (Columns A-F)

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Step 1" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. I am assuming that your drop down list is in cell A1 of sheet "Step 1". Change the target range in the code, Range("A1"), to suit your needs. Close the code window to return to your sheet. I am also assuming that your data in "Step 3" starts in row 59 with the headers in row 58. If this is not the case then the ranges in the code will have to be modified. This macro will trigger automatically when you make a selection
in your drop down list in "Step 1".

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Step 3").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Step 3").Range("A58:F" & LastRow).AutoFilter Field:=3, Criteria1:="0"
    Sheets("Step 3").Range("A59:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Step 3").Range("A91")
    If Sheets("Step 3").AutoFilterMode = True Then Sheets("Step 3").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[Madraykin]

Oooh thanks mumps - will give that a go now

Looks a LOT less complicated than the solutions I've been attempting

Will let you know how it goes, you're a star!

 

[Madraykin]

Right - have inserted it and it's doing *something* (which is more than I got it to do!!!)

Have adapted the code like this:

Private Sub Worksheet_Change(ByVal Target As Range)    If Intersect(Target, Range("I1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Step 3").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Step 3").Range("A58:F" & LastRow).AutoFilter Field:=3, Criteria1:="0"
    Sheets("Step 3").Range("A59:F" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Step 3").Range("A91")
    If Sheets("Step 3").AutoFilterMode = True Then Sheets("Step 3").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

So it is copying the rows and pasting them into the correct place, but it is starting at Row 1 on Step 3 - the dataset I need to copy from has the Header on Row 58, data starts on Row 59 and (at the moment) runs to Row 83 but has the potential to run up to row 150 depending on values that occur earlier on in my process. I have had a root around and can't see anything that is targeting row 1 on sheet Step 3?

The other thing that I hadn't considered is that the data in row 58 onwards is formula data - I don't think I've ever used VBA to 'paste values' - I'm sure there's a way?

The final thing is (and I know I'm making this more and more complicated for both myself and the lovely people who always help on here) - I need this to be a repeatable proposition - basically when I clear the dropdown box ready to choose a different value, I need to be able to choose that new value and start with a 'blank slate' in Line 91 in Step 3 to paste the new variable data (which will then be migrated into another sheet in the workbook to automatically create a doughnut graph - phew!), does that make sense?

Sorry to be a pain..........

 

[mumps]

Without seeing your actual file, I can't see anything in the code that would cause it to start at row 1. I tried it on the data you posted and it worked properly. Is there any data above row 58? Could you post a screen shot of your entire Step 3 sheet? After your explanation, a problem that I can see is that if the data can run to row 150, then you can't paste to row 91. You would have to paste to a row below 150. Would it work for you if the data were to be pasted to the row below the first available blank row below your data. For example, if the data were to end at row 150, could we paste to row 151 or 152 or any row below?

 

[Madraykin]

Hi mumps, thanks for getting back to me, yes there is a mass of calculations above 58 that is boiling down to the shorter table that I want to copy the rows from - have attached a screenshot but, I hope you understand, as there is some sensitive data there I have had to redact some things (I haven't chosen anything in the dropdown so it's mostly blank right now) - EDIT - can only see how to post an image from URL? Am I being stupid, I'm sure I've done it before.......

 

[mumps]

Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Madraykin]

And that will be how I did it before - sorry, brain on a go slow with all this VBA flying around, has been a while!!! Here's a link to the file with redacted data - sorry it's so small, but there was a lot to fit on the page! Any insight much appreciated (you have been wonderful so far!) - I do love learning from you guys, really feel like each question I ask takes me forward in my understanding

https://www.dropbox.com/s/nn83timbkerzujk/Screenshot.png?dl=0

So basically the table at the bottom - anything from C59 downwards may have a 0 - those rows I don't need in my final table, I just need one that has the entire row copied for any C column values from 59 downwards that are greater than 0. It will need to be a paste values situation too. In response to the question about the destination cells, yes I think I was being a bit daft - in this iteration the numbers are not going to change but for the future there may be more 'Digit' groups added that will mean a longer list. The destination 'table' will need to be fixed as I will be drawing data into a Step 4 sheet from it, so I think having it set in stone that it goes from Row 151 downwards would be a good idea. Sorry!!!

 

[mumps]

I'm sorry to be a bother but could you upload the actual Excel file rather than a picture? It would be easier to test a possible solution.

 

[Madraykin]

Unfortunately I don't think I can - it has a lot of sensitive data in there that will take me forever to redact and if I just delete it the whole workbook will break. Let me have a play around with the wonderful code you've provided and see if I can make that work in the first instance - I just don't want to post a link to it here so that everyone can access it........ I'm sorry to have wasted your time.....