Solved: copying data from several workbooks into one workbook

[protegeone]

Hi,
Greetings from a newbie.

Now as I've said, I'm a real newbie and not just here, but VBA wise as well.

I'm trying to sort of collect data from several workbooks into a single workbook ( source would be roughly 200workbooks ).
Both source and destination workbooks have only a single sheet in.

What I'm trying to accomplish is to have all workbooks open in the given folder and then copy value to destination workbook.

Source: A8 cell has a name and F31 has its value ( this is always static ).
Somehow the name in A8 should be matched to the name in destination workbook ( all names are in column B ) and then the value from F31 copied to cell in the same row in column F.

I've enclosed an example, where
csomor gabriella.xls is the source
zaras.xls is the destination

Taking the example,
Source A8 = Csomor Gabriella
F31 = 0 Ft should be copied to F39 in destination

I've googled my head off, but couldn't find solution.
Hopefully someone here would be kind enough to help.

Protege

[geekgirlau]

Welcome Protege :hi:

Try copying this code to the VBE window. Open zaras.xls, and press [Alt-F11] to view the VBE window. In the Project Explorer window on the left (select View | Project Explorer if you can't see it), make sure zaras.xls is selected. Select Insert | Module, then paste this code into the window.

Sub GetValues()
Dim rng As Range
Dim strPath As String


' set the document folder
strPath = "C:\Documents and Settings\Owner\My Documents\Downloads\"

' repeat the same steps for every cell in column B
For Each rng In Range("B2:B" & _
Application.WorksheetFunction.CountA(Range("B:B")))

' was the file found?
If Dir(strPath & rng.Value & ".xls", vbNormal) <> "" Then
Workbooks.Open strPath & rng.Value & ".xls"

' ignore everything after the space
rng.Offset(0, 4).Formula = Split(ActiveSheet.Range("F31").Value, " ")

' convert to numeric value
rng.Offset(0, 4).Formula = Val(rng.Offset(0, 4).Formula)
ActiveWorkbook.Close False

Else
rng.Offset(0, 5).Formula = "File not found"
End If
Next rng
End Sub

[mdmackillop]

Hi Protege,
To speed things up, if you have 200 or so workbooks, you could try adding the following lines to GeekGirl's code.

Application.ScreenUpdating = False -After the 2 Dim statements
Application.ScreenUpdating = True -Before End Sub

The first prevents constant refreshing after evey change and the second resets it on completion
Regards
MD

[protegeone]

@ geekgirlau

You're a genius !http://vbaexpress.com/forum/images/smilies/clap.gif
and I'm speechless..

Everything just worked flawless. Really don't know how to thank you properly for this.http://vbaexpress.com/forum/images/smilies/notworthy.gif
Which would have taken me over an hour doing it manually, now I'm done in a flash.

@ mdmackillop

Thanks for the tip to speed things up.

Even having 200+ files, it took less then 30seconds to finish job.

Thanks again

Protege

[geekgirlau]

Good pickup MD - don't know how I forgot this one! Although usually I add it after I've finished testing.

Protege, you're more than welcome!

[mdmackillop]

Good pickup MD - don't know how I forgot this one! Although usually I add it after I've finished testing.

You're welcome! :friends: