I have posted this in actual Excel forums and I am just trying to get multiple ideas on this.
I have a small project that will be very time consuming if I can't crack this nut. I have to go back through 3 years of Excel files and find the Purchase Request Numbers and/or the Purchase Order numbers off of a manifest form that we use.
There are 637 files and they are all the same format and all in the same folder.
What I want to do is search all the individual files (or workbooks), and extract the numbers that are in Column F. I don't care If I bring over the column header because it is the same in all the WrkBks. I just just do a find, delete on that.
The issue I see in working this out is the number of columns available in Excel 2003 is not enough for the 637 files if I remember correctly.
So therefore the results need to be pasted into the new workbook vertically as opposed to horizontally.
Any suggestions? I figure this is going to have to go the Macro route.
I was playing with something like this and another guy sent me this:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim strFileName As String
'Storing the Path of the main file in path
path = ThisWorkbook.path
'Storing the first xls file present in the folder in strfilename
strFileName = Dir(path & "\*.xls")
If Len(strFileName) <> 0 Then
If strFileName = ThisWorkbook.Name Then
flag = False
lastcolumn = Workbooks(ThisWorkbook.Name).ActiveSheet.Range("IV 1").End(xlToLeft).Column
Workbooks.Open Filename:=path & "\" & strFileName
'Write the code for searching the required column, supposing the column number is 5 then
Cells(1, lastcolumn + 1).Activate
Application.CutCopyMode = False
'next xls file
strFileName = Dir
Loop Until Len(strFileName) = 0
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Appreciate your thoughts guys.