Macro for moving cells with a specific value to another sheet

Hey all, new guy here. *waves*

I did some thread-hunting before posting this and tried to implement a solution without having to ask a question, especially one that already has another thread on the front page, but I was unable to get it to work exactly right and would appreciate some help if anyone is willing. I know there's another thread about this on the main page, and I tried to post there so that I didn't clutter things up, but the OP felt that this was a sufficiently different question as to merit its own thread, so my apologies either way.



While hunting for the answer before asking the question, I came up with a thread from Jerry Beaucauire here that does what I need almost flawlessly. It appends data to existing sheets, creates new ones as necessary, handles the reasonably large amount of data that I'm using, etc. It's an older thread and I didn't want to necro it from back in May.


I've found some difficulty with that solution, though: it works perfectly for 40 out of my 43 unique values, but spits out blank sheets for three of them (interspersed randomly throughout the category column, not having blank cell data in any of the row data) and I was wondering if someone could give me a hand?

The macro that I'm using is the one that Jerry kindly posted to his site here almost letter for letter. The only changes that I've made were to the category column value and the name of the master worksheet.

I think I've narrowed down where I'm having the issue. I shortened my data sample and found that I was having trouble with my longer category names. To my simple knowledge of VBA and Excel, it shouldn't be issue because I'm not coming close to pushing the limits of the String data type (the first issue is a 33-character string), but that's all I can think of and it works flawlessly with almost all of the remaining unique values.

I think that means my data is the problem, since the macro works so well with the majority of the sample.

'Get a temporary list of unique values from vCol
    iCol = ws.Columns.Count
    ws.Cells(1, iCol) = "key"
    
    For Itm = TitleRow + 1 To LR
        On Error Resume Next
        If ws.Cells(Itm, vCol) <> "" And Application.WorksheetFunction _
            .Match(ws.Cells(Itm, vCol), ws.Columns(iCol), 0) = 0 Then
               ws.Cells(ws.Rows.Count, iCol).End(xlUp).Offset(1) = ws.Cells(Itm, vCol)
        End If
    Next Itm

Since it works on basically every other value but those few, I'm assuming I need to sanitize the data a bit? There's no leading space, no special characters besides A-Z and spaces (the spaces not causing issue in other categories), and after that, I'm running out of thoughts because I don't know too much about this stuff.

As an update, I have discovered that the values on which the macro is tripping up seem to have multiple spaces. The first one has four spaces, and when I removed them, the macro populated a new sheet with no problems. Is there a way I can compensate for this so that I don't have to edit the large amount of data I get imported to specific naming conventions? I can't control the naming conventions for the data.

I suppose I could use a new column and toss in a SUBSTITUTE function (SUBSTITUTE, right, not REPLACE?), but I'm hoping I can find a way to account for this in the macro as it stands.

Cheers and thanks in advance,

T

EDIT:

I have been trying different things with the first value that's causing me trouble. I found adding a single space to the name caused it to get looked over. There are over 40 unique values, pretty much all of which contain spaces.

Then, I tried using another value with multiple spaces.

I found that a 24-character cell value with two spaces worked, as did a 27-character cell value with 4 spaces, but a 33-character cell value with 3 spaces did not work.

Now that I see this, I'm fairly sure that it is my data which is the problem, and not just a C+P error when transferring the macro, but I'm not sure I understand why multiple spaces should be tripping up the macro.

Further experimentation reveals that a 31-character cell value with 3 spaces WILL work. Now I'm really confused.

Still playing around. I used the SUBSTITUTE function and that actually did seem to do the trick; it takes like 10 seconds to do, so I'm going to run with that. It's tripping up on a "/" now, so I may just have to control for such things when I input the information, I guess.

OK, I've edited my post a few times to reflect exploration and testing. It's very clearly my data which was tripping up the macro, so I'm adding some preparatory sanitization to the data before throwing it in and that seems to be working. I do not understand WHY that is happening though. I'd appreciate if someone who does could explain it so I can anticipate future problems or maybe amend the macro to catch those issues.

I'm also not clear on the particulars of what is actually causing the problem. It doesn't look like the multiple spaces are specifically an issue, but perhaps in tandem with cell value length? I've discovered one particular non-alphanumeric character that's causing an issue. I'm going to try throwing in a few others to see if it replicates the problem.

So an '@' didn't cause any issues, but an '*' caused the record not to be transferred, just like the '/' did in my earlier attempt (controlling for cell length each time, just in case).

I posted a code blurb above, but that's under the assumption that I'm looking in the correct section. I did link to the full code block, so you can see it there; I have a fairly weak understanding of VBA overall, so it's entirely possible I'm looking really hard in the wrong spot, it's just that it seems to be specifically tripping up on the value for the cell contents getting thrown into the FOR loop. Since the only way I seem to be able to trip up the macro is by changing the values in the first column and that's the only section of the code that appears to specifically handle that value, that's why I'm looking at that.


So that means, if I'm right and if I'm reading the code properly, my data is messing with vTitles. Am I missing something uber-obvious, like values that can't fit into a String variable? I don't think that's it, though, because of the spaces thing.


I'll leave any further edits in this post until and unless someone replies so that I don't unnecessarily bump this thread without substance.

Thanks in advance,

T


EDIT:

I must still be looking at this the wrong way. Just tried pure text, no spaces, but of cell length 34 and that didn't get copied over either.