Macro is not copying to other sheet, just clearing contents of select case

So, I have this loop that I was formerly helped out with on here. It needed slight modification for my purposes, but works like a charm and taught me a lot about the possibilities of the "Select Case" functionality (which is awesome, thank you all who contributed). So I'm trying to help someone in another department solve a similar problem. My data set is very specific every day, where his varies. So, I'm starting a loop that will loop through multiple columns and, as it comes across select case data, will transfer (through offset range from active cell) to an isolated sheet. I feel like I'm really close. When I run the loop for the first row, it grabs all of the range from the active cell and offset and clears it, but does not paste it onto the new sheet.

I'm sure this is going to be a simple fix for the experienced, so I'm hoping the answer is glaring and not a cause of some weird formatting issue with the data set. Any help you can offer will be so greatly appreciated.

Please Login or Register  to view this content.

What is the range that you want to loop through? Cell M3 to what?

That's the thing. The data set always changes, that's why I programed the loop to do until it hits an empty cell. It is supposed to grab the row as defined by Range(ActiveCell.Offset(0, -12).Address & ":" & ActiveCell.Offset(0, 29).Address) and copy it to a separate sheet, then move down one row and, if it meets the criteria set by the select case function do the same, if not continue down the row until it finds another example of the case.

Well, I tried this one and all it did was provide me the first row of data that matched the criteria and added it to the other sheet as many times as the original list is long. Thanks for the help, though, any other thoughts. Why would the original macro posted not add the rows to the copy sheet as is? It seems to be finding the conditional cases and clearing them, just not copying them to the alternative sheet.

I tested your original code and it worked for me?

Ouch, that sucks to know,. I thought there may be an issue with the data set because the original macro, the very first that I submitted, works in an alternative fashion just fine on another spreadsheet with different case criteria. Hmm. Well, I guess there's not a whole lot left to do with this one.

John, I just retried my original macro with a made-up data set and yeah, it clears the rows of the select cases as are found, but does not copy the data, so, there's still something wrong with the original post and it appears to be specific to the .copy sheet function. When you ran the original macro, did it copy to the sheet that you named "import," because it's still not doing that on mine.

Originally Posted by Rokn

John, I just retried my original macro with a made-up data set and yeah, it clears the rows of the select cases as are found, but does not copy the data, so, there's still something wrong with the original post and it appears to be specific to the .copy sheet function. When you ran the original macro, did it copy to the sheet that you named "import," because it's still not doing that on mine.

Yes it did?

I have attached a simple test book so you all can see what the macro is doing. I have it setup to move the select case data to the import tab using a click button found on sheet 3. It is still just clearing the rows where the select case is found, not moving the data. Any thoughts would be greatly obliged. Thanks.

This did work, but when I try to duplicate the code to run it in column "N" it's not doing the same loop.

Please Login or Register  to view this content.

There is no debug error, it just fails to return data if the select case appears in any of the cells in column "N." Any help on how to get it to run this same loop string from row M through AP?

This worked. It's funny, I couldn't get the macro to clear the contents of the row that I was copying in the new macro. The original one cleared the contents, but never copied. So I ended up building the entire macro to move the data and then remove duplicates, rather than clearing the data before the searching the next column for the select case. I really appreciate everyone's help on this. It seems I learn a lot every time I work up a new macro, and with all of your help too. Thanks again for assisting. You've made one of my peers very

Yeah, I removed it because I wasn't able to get it to work properly. As most things go with macros, I figured a work-around with the "remove duplicates" function at the end of the macro. I successfully ran it against a raw data set I was provided and although it worked great, it returned every account in the raw data. Having talked to my co-worker about this, I'm thinking the raw data is already scrubbed by our analytics team before it's presented to him and he didn't know it. So, I learned a lot, but it looks like he may have just had me chasing my own tail.