Open multiple files by looping through a list

I need to do the following:
1.  open last years file from a specific folder for a list of values
2. open this year file from a specific folder for a list of the same values
3. copy and paste last years workbook into this years workbook
4. do some calculations and formatting in the mergered workbook
5. save the mergered workbook into a specfic folder

I have most everything working, I will admit that I am an Excel VBA hack, being self taught by searching out what I need to do from experts here and other internet findings.  My current struggel is if I cannot find a file for LY or a file for TY I want to skip that item in the list and move onto the next item in the list.

My file error method works for my first file, but it does not work for my second file and I don't know why.
tooireland8325DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Surone1Commented:
it would help if you provided us with the actual code ;-)
tooireland8325DirectorAuthor Commented:
Of course it would!!  My apologizes I thought I had attached my file.
CLI-Department-Report-Marco-with-loop.xl
Ejgil HedegaardCommented:
You can't use wildcards in the filename when opening a workbook.
But a Dir command can check if a file exist, and Dir can use wildcards.
I have changed the macro according to that.
The LY and TY files are only opened when both exist.
In column B and C is displayed "LY file exist" and "TY file exist" when the operation is run, and "No LY file" or "No TY file" when it is not.
You have a lot of not needed selections, and lot of repeated operations.
I have put it into one range, so the operation is done on all cells at once.
I makes the macro much shorter, runs faster, and is easier to read and maintain.

Application.ScreenUpdating = False turns off screenupdating, resets automatically when the code stops.
A statusbar (lover left of screen) is added so the progress can be seen.
If you break the code, reset the statusbar with a little sub with one line "Application.StatusBar = False", otherwise the normal Excel messages can not be seen.

I have no files, so I can not test, check using step mode that it does what you expect.

I have shortened the file name because EE does not accept names longer than 40 characters.
CLI-Department-Report-Macro.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.