Copying cells from 194 workbooks

[oreo haven]

I have 194 Workbooks that all contain data on the first sheet only. I need to go into each sheet and pull the information from cell B6 and F21. That information needs to be copied to a new workbook into columns A and B so when I am done I have 194 lines in two columns.
Each of the workbooks I am pulling from have the info I need in Sheet1.

Is this possible, or do I need to do a lot of copying and pasting? If it is possible, how do I do it? DOes anyone have any ideas?

Thanks,

Jon

 

[mole999]

may seem a silly question, how would you know what came from what workbook

 

[oreo haven]

The workbooks each contain a form that our employees use to set up a machine. Cell B6 has the unique part number, and F21 contains the cycle time. If there is a way to do this and another column is needed with the workbook name, then that is not a big deal... I know that this is kinda off the wall, but I figured that there must be an easier way that copy and paste. Especially since if there is an easier way, then I can change the macro/vba later and use it to gather all the information and use Access to store it and print the forms. For right now I am in a time crunch. I have to have all of this information before the end of the work day.

 

[oreo haven]

I forgot to add that I am using Excel 2000. And all of these workbooks are stored in one folder....

 

[mole999]

there are numerous posts on here about accessing multiple workbooks, and i'm not skilled enough to give you a quick answer

 

[oreo haven]

I used the following code and I was only able to get 193 lines of data. I ran the code twice...once set up for pulling from B6 and placing it in column A and one for pulling form F21 and placing it in column B. Does anyone know why I missed one file? How would I change this code to be able to copy both cells at once?

Sub test()
Dim myDir As String, fn As String
myDir = "C:\Documents and Settings\jsd\Desktop\400_TON\ALL SHEETS\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
With Workbooks.Open(myDir & fn)
With .Sheets(1).Range("F21")
ThisWorkbook.Sheets(1).Range("b" & Rows.Count).End(xlUp)(2) _
.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
.Close False
End With
End If
fn = Dir
Loop
End Sub

 

[mole999]

brain is saying one is not a valid workbook, or we need a plus one (+1) somewhere in the formula to go the extra step

 

[FundamentalDiscord]

You aren't including "ThisWorkbook" in the 194 are you?

Your code says to skip ThisWorkbook:

If fn <> ThisWorkbook.Name Then

Do you have 194 Workbook in addition to "ThisWorkbook" or 194 including it?

 

[oreo haven]

The joke was on me. I started looking through each workbook to find out which one was skipped. There was 1 workbook that had blank fields for both of the cells I was copying. No I just need to figure out how to run this for multiple cells at one time, I am very new to macros and everything that I have ran so far has been blatantly plagiarized. All I do is modify one or two lines so it will hopefully work for my purposes.

 

[mole999]

The joke was on me. I started looking through each workbook to find out which one was skipped. There was 1 workbook that had blank fields for both of the cells I was copying. No I just need to figure out how to run this for multiple cells at one time, I am very new to macros and everything that I have ran so far has been blatantly plagiarized. All I do is modify one or two lines so it will hopefully work for my purposes.

going back to my original question then would be how do you work out where the data came from, if you could trap that it might give you a clue if you have book name and no data