Automatic alphabetic data extraction macro needed

[TheAssetMgr]

I came across someone in the office today that has been spending huge amounts of time on unnecessary copy/paste/sort gymnastics and it hurt my soul to watch. I know you geniuses can help me/her out so here's what she's doing...

She's working with 3 spreadsheets; I'll call them Initial, Working, and Final.

The Initial file has all the data (and then some) that needs to end up on the Final spreadsheet but the kicker is that the Final spreadsheet has 26 tabs, A-Z and data copied there has to be in a certain tab based on a name. The user is copying 6 columns all at once from Initial to Working, sorting the Patient Name column in Working alphabetically, copying all the As to the Final spreadsheet and then repeating for each letter until there's no more data in Working.

What I'm looking for is a macro that can be executed that will look at Initial, find all the Patient Names beginning with A, copy the required columns of data from Initial to the Final spreadsheet, tab A... and then repeat for Patient Name values beginning with B, and so on.

The Working file the user created was done so the Initial data didn't have to be touched while being worked on. Just a standard CYA working file is all it is. No need to use one in the final solution if it's not necessary.

Any thoughts?

 

[Fluff]

That means you don't have a sheet in the "Final" workbook called whatever the value of Ky was at that time.
Are the sheets called A, B, C etc?

 

[TheAssetMgr]

Yes, the sheets in the Final workbook are named simply A, B, C, etc...

What does Ky refer to please?

 

[Fluff]

It's a variable that refers to the Keys in the dictionary.
Does any data get copied to the relevant sheet, or does it fail before anything is copied?
When it fails have a look at the data sheet & what sort of values do you have in col C?

 

[TheAssetMgr]

I added my name at the bottom of the Initial workbook's list and it DOES show up on the S tab of the Final workbook. So that's definitely a win there.

The data sheet, and I should have mentioned this before, has many tabs but only the first one called Triage Data needs to be looked at for the six columns. I noticed there was a filter in place on the Initial data set so I removed it and reran the code - still broke with the same error.

The data in the Patient Name field appears to be just first and last names (and some middle names) separated by a space. There are a few periods in there where "Jr." appears and I saw one "-" in a hyphenated name. No numbers or special characters that I could see but it was just a quick skim of the 6k records. Does the formatting of column C matter at all?

 

[Fluff]

The sheet you want to copy the data from needs to be the active sheet when the code is run.

That can be changed, but is the sheet called "Triage Data"

 

[TheAssetMgr]

The active sheet, just prior to clicking over to the VBA editor, is the Initial workbook, first tab.

I'm running the code from the separate VBA editor window - does that matter?

 

[Fluff]

No, that's fine as long as the active sheet contains the data you want to copy.

Will you always be copying data from the same sheet & does it's name change?

 

[TheAssetMgr]

The active sheet will always contain the data we want to copy and it'll always be the same sheet name and workbook name.

Not sure if it matters, or if I've already mentioned it, but the Final workbook will always have data in the first tab that needs to be overwritten each time the code is executed.

Is there anything else we can poke at to nix the error this is throwing?

 

[Fluff]

If you add the line shown

Sub AssetMgr()
    Dim Dic As Object
    Dim Cl As Range
    Dim Ky As Variant
    Dim Wbk As Workbook
    Dim Ws As Worksheet
    
    Set Ws = ActiveSheet
    Set Wbk = Workbooks("Final.xlsx")
    Set Dic = CreateObject("scripting.dictionary")
    
    Dic.CompareMode = 1
    For Each Cl In Ws.Range("C2", Ws.Range("C" & Rows.Count).End(xlUp))
        Dic.Item(Left(Cl.Value, 1)) = Empty
    Next Cl
    For Each Ky In Dic.Keys
        [COLOR=#ff0000]Debug.Print "|" & Ky & "|"[/COLOR]
        Ws.Range("A1:U1").AutoFilter 3, Ky & "*"
        Wbk.Sheets(Ky).UsedRange.ClearContents
        Intersect(Ws.AutoFilter.Range, Ws.Range("C:D,I:I,P:P,R:R,U:U")).Copy Wbk.Sheets(Ky).Range("A1")
    Next Ky
    Ws.AutoFilterMode = False
End Sub

It will print the value of Ky to the immediate window. If it's not showing Ctrl G will bring it up ( it's normally found below the code window)
When the error occurs what is the last value in the immediate window

 

[TheAssetMgr]

I inserted the line right where you have it and ran the code. Same error appeared, and after clicking debug it's pointing to the same line. Last value in the Immediate window is |W|.