Questions:

1. How would the macro  identify the collections of lists  to combine?
2. Are you just appending each list to a master list? No other
manipulation of data needed?

I'm not sure I understand your first question. I think the macro
could identify each group according to the numbers on the # column. I
don't want to include blank rows.

And yes I'm just appending each list to a master list. I don't need
any manipulation of data. The problem I have is that sometimes the
number of people in the lists  may be different. Another thing that
troubles me is that I have some columns between the ones I need. For
example in this table:

# USRID NAME D1 D2 D3 D4 TOTAL
1 545563 JANE 5.0 5.0 5.0 5.0 20.0
2 105654 MIKE 5.0 5.0 5.0 5.0 20.0
3 541586 STEVE 5.0 5.0 5.0 5.0 20.0
4 343423 MARK 5.0 5.0 5.0 5.0 20.0

I would only need the USRID, NAME, and TOTAL columns.

I wasn't referring to data within a worksheet. How would the macro
know which lists  to combine? Which directories, which files, which
workbooks, which wooksheets?

I'm assuming a given worksheet  would only contain one list, and only
contain that list. Per your other answer, each list would contain AT
LEAST the columns of data you need, but could contain more?

I get it know. I don't need the macro  to combine lists  from different
files. I'll do that myself. I just need it to combine the lists on each
file (with the groups). I need to combine them without the blank rows
and also the problem that I mentioned earlier, that some lists (groups)
may have different number of people every week.

Each worksheet  contains like 3 sublists. Those are the ones I need the
macro to combine. In the first example I didn't include all the columns
that appear on my table, sorry. They always contain more than the
columns I need. The columns are not all next to each other, that is
what I wanted to illustrate on the second example.

I think too much depends on the details...

For example, if I use the two lists  you gave originally:

> GROUP1
> # USRID NAME SALES
> 1 545565 JOHN SMITH 599.0
> 2 105654 NORMAN ANDERSON 599.0
> 3 541586 ANTONIO BANDERAS 599.0
>
> GROUP2
> # USRID NAME SALES
> 1 545563 JANE 599.0
> 2 105654 MIKE 599.0
> 3 541586 STEVE 599.0
> 4 343423 MARK 599.0

I can just do a database query, without a macro, to get create this
output of data:

> F1 F3 F4
> 1 JOHN SMITH 599.06
> 2 NORMAN ANDERSON 599.06
> 3 ANTONIO BANDERAS 599.06
> 1 JANE 599.06
> 2 MIKE 599.06
> 3 STEVE 599.06
> 4 MARK 599.06

But, as they say, the devil is in the details.