David Ringstrom
Member Since: Jan 6th 2014
Columnist
Likes: 0
Thanks: 1
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.
My answers
Thank you for your kind feedback!
The only way to do this is to write a macro that would assign a unique name to each worksheet. The numbers appear so that each sheet has a unique name and so there's no other way around this.
A VBA macro in Excel can stack everything together into one list. This is a common enough need that I'm sure a Google search would unearth a Combine Sheets macro.
I'll be honest, whenever I find anyone using 100 sheets for *anything* in Excel, it's typically time to look to another solution. But Excel may be what you have for a tool. In that case, if it were me, I'd try assembling as many sheets as I can into a single worksheet, and then start a second worksheet to combine together as many additional sheets as I can. I'd then use a third query to combine the two "combined" worksheets into one list.
No, I don't think the problem is with height or width or any other features, except the Filter. Excel's Filter feature could put the workbook in a state where you can't combine the sheets. I'd try turning it off and see if that helps as a rule out. If you have the same problem afterwards, then you can skip that.
For the Administration worksheet, I'd try using Microsoft Query to pull that data into a separate worksheet by itself. If it lets you retrieve the data, copy and paste the SQL statement into your existing query with the Union statements.
It's tough to diagnose without seeing your data, but setting it down and picking it up again with a fresh eye does wonders for tracking down nuances in one's data.
Laurie,
I set up a file with the name you used and the sheet names in question, along with some sample data. I was able to get everything to work and I didn't get that error triggered.
It could be that your workbook has some corruption in it. As a starting point I'd copy and paste your data to a brand new workbook and try again to see if it works. If it doesn't, I'd be happy to take a look if you can share your workbook with me. Feel free to remove any confidential data. You can upload the file by way of the Upload a Project file link at www.acctadv.com.
David