Macro To Pull Data From Many Workbooks To A Master Worksheet

[JimmiOO]

Can someone help me out with this please.

I have attached my master sheet and one of the many workbooks that I want to pull data from

I have my master worksheet in a folder also in the folder is 2 sub folders called Jsy & Gsy

In these sub folders I have lots of workbooks all called different names, but they all have the same worksheet within them which is called “Stock” (sample attached)

What I would like is a Macro that would open these folders and go through all the workbooks and pull back data and insert that data in to my master sheet

What I am looking for is to find out how many times a product has been listed and what the combined value is


On my attached Master Sheet I have Columns A – F

On my attached sample sheet (031212) I have columns A - V


Master Sheet Column A = In this column I would like all the codes that appear on Column B on all the workbooks in the sub folders.

Master Sheet Column B = In this column I would like all the descriptions that appear on Column C on all the workbooks in the sub folders.

Master Sheet Column C = In this column I would like all the sizes that appear on Column F on all the workbooks in the sub folders.

Master Sheet Column D = In this column I would like count how many times the code in Column A (Master Sheet) has been listed

Master Sheet Column E = In this column I would like a combined value, the value can be found in Column T on all the workbooks in the sub folders.

Master Sheet Column F = In this column I would like to list the name that appears in Column U on all workbooks in the sub folders


When a code is added into column A in the master sheet, I do not want it to appear again, I just need the Columns D & E updated each time, so it may appear more than X amount of times which will be listed in Column D and a combined value of all the times its listed will be Column E

I have added one entry in the master sheet to show you what I mean, 01398 is listed 2 times in the sample sheet with a combined total of £88.00

Hope this makes sence, and thanks for taking the time to read this.

Many Thanks in advance for any help

[Richard Buttrey]

Hi,

Is it vital to record how many times each code is listed, since at the end of the day you appear to be wanting a unique list of codes with their associated total values.

If it were me my preferred approach would be to simply combine all the relevant rows and columns from the stock sheets into the master and then use a Pivot Table to analyse the data and show you summary values, count of codes by file, count of 'Actioned by' by file, and just about any other analysis you want.

That could be done very quickly. The macro below will consolidate the data and do just that

All you then need to do is create a Pivot Table to give you your summary.

Sub ConsolidateFileNames()
    Dim stDirectory As String, lCounter As Long, stFolder(2) As String, x As Long, lLastrow As Long
    Dim wbMaster As Workbook, wbTemp As Workbook
    Dim FSO, ofileItem, oSource
    stFolder(0) = "jsy"
    stFolder(1) = "gsy"
    Set wbMaster = ActiveWorkbook
    Set FSO = CreateObject("Scripting.FileSystemObject")

    For x = 0 To 1

        stDirectory = ActiveWorkbook.Path & "\" & stFolder(x) & "\"
        Set oSource = FSO.GetFolder(stDirectory)
        Application.DisplayAlerts = False
        For Each ofileItem In oSource.Files
            lCounter = lCounter + 1
            Sheets("Master").Cells(lCounter, 9) = ofileItem.Name
            Workbooks.Open Filename:=ofileItem
            Set wbTemp = ActiveWorkbook
            lLastrow = Range("B" & Rows.Count).End(xlUp).Row
            Range("B12:C" & lLastrow & ",F12:F" & lLastrow & ",T12:T" & lLastrow & ",U12:U" & lLastrow).Copy
            wbMaster.Sheets("Master").Range("A" & Rows.Count).End(xlUp).Cells(2, 1).PasteSpecial (xlPasteValues)
            wbTemp.Close
            Application.CutCopyMode = False
        Next ofileItem
    Next x
    Set FSO = Nothing
End Sub

Regards

[JimmiOO]

Hi thanks for the reply, it is vital that i know how many times a code is listed. I have 2 things i need,

1 = Is to know how many times a code is listed within all the worksheets, the reason is i am trying to cut down on the amount of times a code appears and need to keep a record of my progress

2 = I need to know the combined value of each code

Also the only worksheet that needs looking at is the "Stock" sheet within each workbook in the folders

Thanks

[Richard Buttrey]

Hi,

Both of which you will get in seconds from a subsequent Pivot Table once you've run the macro. Can I suggest you run it, then if you want help with the PT, post back.

[JimmiOO]

This is the results i got back from your macro, all looks good. As i said i am not familiar with Pivot Tables, how would i go about getting the info i need with PT?

Many Thanks

[JimmiOO]

Hi Richard i have run your macro and now have a long list with all the data, I am not familiar with Pivot Tables though, would it be possible to guide me throught it please?

[Richard Buttrey]

Hi,

See the attached.
In order to get some data to work with I replicated your stock sheet three times which is why you see everything triplicated on the Master tab.

On the Pivot table tab you can see how the values have been totalled by code and how the numbers of codes have been counted and totalled by code. As a bonus you can also see a drop down for the Buyers at the top. If you click on this drop down you can choose a particular buyer and the Pivot table will update automatically.

I'm also attaching a picture of the Pivot Table when the Buyer field has been dragged and dropped into the table itself so that the data is analysed at the higher level of Buyer.

Hopefully you can see how powerful a PT is. You can analyse in ways you've not yet thought of. It will pay you half an hour or so to learn about them. Doing it this way, i.e. loading all the data into the master tab and using the PT avoids complicating any load data macro by having to recognise which codes have already been loaded, and only include the value in a running total for that code. That would be quite messy.

[JimmiOO]

Hi Richard

Thanks very much for all your help so far, i now agree the pivot table is the right way to go, but i am still getting to grips with it, i have attached what i have done so far, but it does not look like yours and i can't seem to get it right. On mine after each code i have a total, and after each buyer there is a total but this does not appear in yours. Would it be possible to have a look at mine and let me know what i am doing wrong?

Thanks

[Richard Buttrey]

Hi Richard

Would it be possible to have a look at mine and let me know what i am doing wrong?

Thanks

Hi,

1. In the PT Options (right click in description column of the PT or from the Ribbon PT Menu) untick Subtotal Description.
2. Choose do not show subtotals

I've also:
3. Added an initial line to the macro which will clear the existing data before loading a new set from ALL the files, and switched off the screen updating during the file load.

4. Created a dynamic range name "Data" for the data so that the pivot table will always use the whole data without needing to specify all 65000 rows.

5. Finished the load macro with an automatic refresh of the pivot table.

[JimmiOO]

Hi Richard after playing with it for a bit i have sussed it, many thanks for all your help. Problem Solved

[kevincwk2000]

Hi Richard,
I have similar request thus I tried to learn from Jimmi examples (master.xls, 031212.xls) with your macro but the macro cannot be run.
Any idea?

[Richard Buttrey]

Hi,

Almost impossible to say without knowing more about your macro.

However unfortunately this post does not comply with Rule 2 of our ForumRULES. You shouldn't post a question in the thread of another member -- please start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[kevincwk2000]

thanks Richard,
Sorry, I do think it would be the quickest way to ask the question from the macro orginator.
You might miss to read the created new thread or other user might response on your macro, it seems relative less efficiency to solve the problem
And I run the exact macro from you onto the Jimmi's examples. but no PT was generated.

[Richard Buttrey]

Many people say they have a similar problem but experience shows that more often than not there are fundamental differences, hence we ask you to start your own thread and if necessary include a link to what you think is a similar thread.

It would appear that you probably haven't read the macro since you are assuming it creates a Pivot Table for you. It doesn't. As post #2 clearly indicates. The PT was manually added afterwards.

You do have a different requirement which is to know how to generate this PT, which is why I suggested you do start your own thread in accordance with the forum rules.