Thread: Get data from diferent workbooks to a single workbook

Hi all
I have a problem that if anyone could help me to solve i will be thank-full
i have several workbooks in one folder with information regarding stocks of goods in different articles, these sheets have always the same name ( existencia ), and the information i need to " get" is allways in the same range(s) ( in yellow )
i need to take the information that is in yelow from the diferent workbooks to a workbook called stock.
I made an exemple of what is my target
thanks

attatched the exemple files

This will do it, just edit the fPath at the top of the macro to point the correct folder. This macro goes into the stock.xls file in a standard code module.

code removed...see below

Hi Jerry, first of all Thanks for your help.
I am testing it right now !

Your data isn't what it appears to be. Even filtering for values >0 still gets some rows that appear to be zero because it's only displaying zero. After the import is complete click on one of "zero" cells that still got imported and you'll see the value in the cell isn't actually zero. So you've got some problems with your data you'll have to resolve.

I've edited the macro to filter and copy only values >0, perhaps you can safely change that to >2 and still get the proper results?

Option Explicit

Sub CollectInfo()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/21/2010
'Summary:   Collect specific data from all workbooks in a single folder

Dim fPath As String:        fPath = "C:\2010\Test\"      'where files are found
Dim fName As String
Dim wbData As Workbook
Dim wsDest As Worksheet:    Set wsDest = ThisWorkbook.Sheets("stock")
Dim NR As Long:             NR = wsDest.Range("B" & Rows.Count).End(xlUp).Row + 1
Dim LR As Long

Application.ScreenUpdating = False      'speed up macro
fName = Dir(fPath & "*.xls")            'filter for files to open

Do While Len(fName) > 0
    Set wbData = Workbooks.Open(fPath & fName)  'open found file
    With wbData.Sheets("existencia")
        .Rows(10).AutoFilter
        .Rows(10).AutoFilter Field:=6, Criteria1:=">0"
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        If LR > 10 Then
            wsDest.Range("A" & NR).Value = .[A4]
            wsDest.Range("D" & NR).Value = .[D2]
            .Range("A11:A" & LR & ",F11:F" & LR).Copy
            wsDest.Range("B" & NR).PasteSpecial xlPasteValuesAndNumberFormats
        End If
    End With
    wbData.Close False
    NR = Range("B" & Rows.Count).End(xlUp).Row + 1
    fName = Dir
Loop

LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:D" & LR)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With

Columns.AutoFit
Application.ScreenUpdating = True
End Sub

I'm glad I was able to help.

Regarding your post here:
http://www.excelforum.com/excel-prog...data-from.html

1) It is customary to ask followup questions in the same thread, though your question is unique enough perhaps that's no biggie.

2) I am concerned that you find it necessary to remove the authorship code lines from the macro especially before posting it again on the internet. That is very odd to see. Was the help given not sufficient to warrant leaving that part alone?

Don't get me wrong, if you write your own macro(s) using snippets and techniques learned here, then of course, it's ultimately your work. But the only change you made was to adjust a number (like I instructed) and remove the crediting comments.

Very odd to see. Especially when you then post the macro again somewhere else, in which case the source commentary is specifically appropriate, don't you think?

Perhaps he (or she) is expected to not be asking questions on the internet at work, in which case it seems quite reasonable to me. Since you're giving the code away anyway, why do you care (or indeed put your name to it in the first place), if I may ask?

On all the various forums I frequent fully fleshed macros are quite often authored, while snippets are not.

It's just as reasonable to leave them in. On code this complete, that is. If not left in at work, then definitely when posting back for more. It's the wee small things, I guess.

Originally Posted by JBeaucaire

On all the various forums I frequent fully fleshed macros are quite often authored, while snippets are not.

I guess we must move in different circles then.

It's just as reasonable to leave them in.

Again, if this is a work thing, I can totally understand why someone would not want to make it obvious that they did not actually write the code!

At the end of the day, I guess I just don't see the point in worrying about it, unless someone is stealing something you charge for. Different strokes, though, and all that.

Originally Posted by romperstomper

I guess we must move in different circles then.

Nah, I can see you from here. I like that sweater.

Again, if this is a work thing, I can totally understand why someone would not want to make it obvious that they did not actually write the code!

We've already agreed on that...can only go by what is seen here, though.

At the end of the day, I guess I just don't see the point in worrying about it, unless someone is stealing something you charge for. Different strokes, though, and all that.

That's ok, too. Different strokes indeed. I hope you hear me saying I understand your comments, I don't agree, that's all.

Thanks - my Granny knitted it for me.

As to the rest, I hear ya, and I do understand - we all have things that get under our skin, however niggling they may seem to others. I guess really all I was trying to say is that I've rarely seen any good come of expressing those frustrations, so now I just ignore people who annoy me (as much as I can bear to, anyway! )

Originally Posted by romperstomper

So now I just ignore people who annoy me

Will you start speaking to me again if I say sorry

Dom

Only if you say you like my jumper.