Loop through sheets on multiple workbooks

[CalRich1023]

Hello!

This could be a bit complicated, but I am going to try to explain this as best I can.

I need a VBA code to loop through most (not all) sheets on about 10-12 different workbooks (I can add more WB names to the code as they are created) and extract a varying range of cells that will be anywhere from 1 line to 100 lines of data per sheet. Maybe it could be helpful to put all necessary workbooks into a dedicated folder, and then search the entire folder? I want this data to be copied into a new workbook (where the code will be written) so it can be further analyzed, all in one place. There are going to be 100's, maybe upwards of 1000 sheets in total to loop through when it is all said and done.

• The range will begin in cells J9:P9 and continue downward with a different number of lines on each sheet (end.xlup maybe?)
  • This will be the same starting cells for each sheet.
  • It may be important to note that this range does contain an array, so this may throw a kink in things.
• The Sheet Names that need to be EXCLUDED from the search (every workbook is the same) are:
  • MASTER PIVOT
  • Table Of Contents
  • SEARCH
  • COPY TEMPLATE
  • ACCT #'S
  • DATA
• The Workbook names that need to be searched are:
  • Inventory_1
  • Inventory_2
  • Inventory_3
  • etc.

I hope this makes sense and I REALLY appreciate all help that anyone can provide. This is an outstanding community that has helped me sooo much in the past and I look forward to more assistance with this project. Thank you all so much!

 

[Marc L]

Hi,​

​

this is a common subject you can find whatever the Excel forum …​

​

But as often it's the basic beginner way - open a workbook, copy sheets data, close the workbook - so according to your description​

this kind of VBA procedure may last a while !​

To speed it up an advanced way like using some SQL via an activeX object should be used but as it depends on how smart are designed the worksheets …​

​

For further assistance as here this is just an Excel forum and not some mind readers one so to explore any way we need a zip file​

containing some representative source workbooks and accordingly an accurate expected result workbook where the VBA procedure will be located.​

Link this zip file on a files host website like Dropbox …​