extract range has a missing or illegal field name

I saw some threads with advice on how to fix this, but nothing helped. I'm also very new to VBA and creating macros, so I have a feeling this will have more bugs.

Basically, what I want to do is separate the data in Sheet 1 by category 'EmployeeNo.' After that, I'll also need a second macro that consolidates this data back into the same file, but I'll work on that later.

Any help would be greatly appreciated. Thanks!

File1.xlsm

By the way, I updated the attachment, but am unable to remove the previous one, so please follow the link 'File1.'

Basically, what I want to do is separate the data in Sheet 1 by category 'EmployeeNo.'

Care to elaborate?

This code is populating the array with the values from column L, "Current Year Budget", including the header row.

Please Login or Register  to view this content.

Workbooks.Open Filename:=wbHere.Path & "\" & myArr(Cnt, 1) & ".xlsx"

Read my post again, look at the data in your spreadsheet, specifically regarding what I said about the data that's being put into the array, then look at what you just posted.

Please Login or Register  to view this content.

The file you uploaded in post #5 has in column L of Sheet1 some data.
The header is "Current Year Budget", then there is some data, 400, 275, 400 again etc.

In that same post you have this:

If anyone has a chance, please look at the new attached workbook and see if you can find what's wrong with the 'ConsolidateFiles' macro.

That code is from that macro. MyArr is an array of values.
The values that are filling that array are, because of the way the code is written, coming from that column.

Therefore the first item in the array is "Current Year Budget", the second item is 400, the third item is 275, look at the file you posted in column L.

Now the code is trying to open a file

Please Login or Register  to view this content.

Notice where is says myArr? So the first time it loops it's looking for a file that ends in Current Year Budget.xlsx, you say your error is Row Labels.xlsx, so I think you are not using the file you posted because I don't find the text rowlabels anywhere in the file, but look at your sheet and find that text and that's why you are having an issue. In the file you posted where is the list of file names you want to open?

I reused some of Jindon's code so hopefully it works the first time.
If you have any problems copy and paste the line of code that errors, it will be the one highlighted in yellow.
Try this on a copy.

Please Login or Register  to view this content.

Jindon's code created new workbooks with one tab each in them. This is simply opening the workbooks by using your sample sheet to create the name of the workbook to look for, opening it and copying the existing tab as is. It's not naming anything, it's pulling in sheet number 1 which already has a name, not making decisions to pull in the wrong category or the right category, just copying in sheet 1. I assume it's the only one since the original code only created one tab.