It's a long while since I used SQL into workbooks, so someone else might
have information readily to hand.

However, IIRC your SQL needs to refer to a workbook  file. I.e. it won't
look at information in a workbook that's open in RAM.

It is likely that the SQL would be able to refer to the copy of the workbook
that is on disk while you are running the code  from the copy of the workbook
that is in memory, but there is always the potential for these to be out of
step, so that's a real risk.

If you want to use SQL, then my thought is that you should use a second
workbook to access the one with the data  in it. If you want to stay inside
one workbook, then don't use SQL - simply use code and/or formulas.

You can do your union with a subroutine that is simply given a worksheet
name in its call and goes and gets the information from that sheet  and
appends it to your result sheet. Call this as many times as there are
sheets - from a master subroutine or button event handler. Once they
finish, sort the information as needed.

If your union is working on differently-formatted "tables", then you'd
probably have one subroutine for each "select" in the SQL and call them
appropriately from the master.

Note that I also seem to recall that SQL access to Excel is not very good at
referring separately to multiple worksheets in the one workbook. But it's a
hazy recollection and possibly even obsolete.

In essence, if it's Excel reading Excel, use code, not SQL. If it's Excel
reading Access (or any other DB), use SQL. If it's Access reading Excel,
use SQL in preference to code.

Or if I want to do this in a really roundabout way, first export the
data (with pre-processing to standardise the columns etc) to Access,
and then bring it back from Access to Excel.

Ok, ta. You've at least diagnosed the first major issue with my code.

Have you considered creating  the pivot table  directly, using multiple data
ranges (your bank account data  sheets)? Depending on the layout of your data, it
may not be necessary to combine the data before creating the pivot  table.

> Have you considered creating  the pivot table  directly, using
multiple data  ranges (your bank account data sheets)? Depending on the
layout of your data, it may not be necessary to combine the data
before creating the pivot  table.

Quite a lot needs to be done in reorganising the data. For example
some of the bank statements  contain credits and debits in separate
columns, others have one column with positive and negative values.
I'd need among other things to consolidate these into a single
transactions column. There are a bunch of other things like that
which need doing also.