This my first post in this forum, and I am newbie to VBA. I thought to ask you whether you might have some ideas how to do the following task I am trying to accomplish in VBA:
I have more than 600 workbooks in a folder containing multiple spreadsheets. Each of spreadsheets contains some sort of data which I need to access their range and copy to new workbooks.
From sheets stored in one workbook for example I only need one, which is predominantly stored at Sheets index position 1, although in more than 100 workbook that is not the case. I already looped through all files in folder with Dir() function and know which workbooks do have sheets that I need on wrong index position. I need also to copy a range of that specific sheet to another workbook, which I am able to do but how to use VBA to find dynamically workbook I need and sheet by its index position. I really want to avoid opening workbooks via Dir function in a loop and do work manually.
How can I dynamically use an array or any other method and find those workbooks and sheets within workbooks that I am interested and either flag them through Boolean If statement and either have msg box pop out saying: “This sheet needs work”, or just copy the range to new workbook and save it along with the remainder of workbooks in a folder.
Any ideas or help would be much appreciated.
Many thanks