UnsolvedCoding
Technical User
I have some old code to do this but its crude and slow.
In a folder are over 400 workbooks and each of the workbooks has tabs that start with io. I need to get into each workbook, find the right tab, pull the info from a set of cells and then close the workbook and repeat.
Because the workbooks were copied over to a folder and are not originals I don't need to worry about screwing something up.
What is the most efficient way to loop through them and extract the cell data?
This is what I have so far -
In a folder are over 400 workbooks and each of the workbooks has tabs that start with io. I need to get into each workbook, find the right tab, pull the info from a set of cells and then close the workbook and repeat.
Because the workbooks were copied over to a folder and are not originals I don't need to worry about screwing something up.
What is the most efficient way to loop through them and extract the cell data?
This is what I have so far -
Code:
Option Explicit
Public directory As String
Public fileName As String
Public sheet As Worksheet
Public i As Integer
Public j As Integer
Sub Extract()
On Error GoTo Error_Handler
Application.ScreenUpdating = False
directory = "C:\Users\Desktop\Copys of IO List\"
fileName = Dir(directory & "*.xl??")
Do While fileName <> ""
j = 1
Workbooks.Open (directory & fileName)
For Each sheet In Workbooks(fileName).Worksheets
' Only check sheets that starts with IO
300: If Left(UCase(Workbooks(fileName).Worksheets(j).Name), 2) = "IO" Then
'Copy columns A, C F and I to this workbook
1100: End If
j = j + 1
Next sheet
Workbooks(fileName).Close
fileName = Dir()
Loop
Application.ScreenUpdating = True
1300: Exit Sub
Error_Handler:
End Sub