Open multiple files by looping through a list

tooireland8325
tooireland8325 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
it would help if you provided us with the actual code ;-)
tooireland8325Director

Author

Commented:
Of course it would!!  My apologizes I thought I had attached my file.
CLI-Department-Report-Marco-with-loop.xl
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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial