Extract summary data from multiples spreadsheets

[Kmahraz]

Hello -
Trying to automate a manual task that's been a pain for so long where I receive several excel files with specific data related to some products, once I receive the excel records from our customers I place them in a specific folder, these files are named in this fashion “ CustomerName_2018 Q1 – Reporting”. Then I use an excel Master file to capture some information from specific cells QTY’s, $$ Sales, and Customer name for each quarter.

Would like to get some suggestions, or help with VBA code that will extract the data from the excel files and populate a table/summary for each of the customer. It’s basically my 1st step in creating a dashboard.

Reference attachments for examples of what I am trying to achieve

Any help will be much appreciated!

 

[Kmahraz]

Wanted to clarify my request,

I would like to see if its possible to extract 2 values from each closed workbook that contain the customer name in a specific location.

Example:
Chandoo Master.xls
sheet Sales Product 1
Row A2 Contain Customer 1
when I run the code, I would like that it extract 2 values from a closed workbook that its title contains Customer 1 and do the same thing for each customer name within column A.
The values will be taken from
Cell D26 (Source file) will be pasted in cell C2 (Master file)
Cell E26 will be pasted in cell D2 (Master file)
Any help will be much appreciated!

Thanks!

 

[Marc L]

Hi !

Instead of a code you can apply a formula …​

 

[Kmahraz]

Thanks, @Marc L for the suggestion.
Yes formula will work, but I am planning to integrate with a userform I created with other features and I am missing this part.
Any help will be much appreciated!

 

[chirayu]

I have multiple questions
1) Will there be more than 2 Products in Source file/ Master file
2) Will there be more than 1 file per Customer in same folder e.g. Q1,Q2,Q3,Q4
3) Is the Product sheet name in master always the same as product sheet name in source file
4) Source filename "Customer_1_*" or "Customer 1_*"


If 1 or 2 is true and 3 is false then macro will be complicated

 

[chirayu]

Attached is a sample macro.

Assumes the following:
1) Master Product names are the same as Source Product names
2) Source file starts as "Customer 1_*" rather than "Customer_1_*" (basically though customer name should be same as what the filename says so if there are spaces in master then there should be spaces in source filename)
3) There is only 1 file per customer in the folder i.e. either Q1 or Q2. not both

Accounts for:
1) Multiple varying products per customer as not every customer may have the same product/ all products

Change FPath to your folder path
When you run the macro it will ask you to put in the quarter for which you need to put in data 1/2/3/4. that's how it figures out which column to paste in

 

[Kmahraz]

Thank you @chirayu

It works for the first product but not the second product,

Technically they will be multiple products that will be added each time it becomes available and each product will have an identical spreadsheet in source file titled the same. Also, in the source file, there's a FAQ’s sheet that has some instruction that sheet is only for our customer to inform them of some questions related to the product.

 

[chirayu]

The FAQ sheet shouldn't matter much as it reads the sheet names in the Master files & uses the sheet name as a Variable when referencing to sheets in source file.

Should work for multiple products as it worked on the original sample files. Only thing I can think of is that the Source file sheet name and Master file sheet name are not the same, or the sheet is not there in Source file.

What I mean by sheet names not being the same is e.g. Master says "Product 1" whereas source says "Product1", or Master says "Product 1 " and source says "Product 1" or Master says "Product 1" and source says "Product 1 blah" etc.

For scenario 3 where there is extra stuff on the end - you can modify the code & use the LIKE statement to select the sheet before loading the values into QTY and USD e.g

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*sht*" Then
        ws.Select
        Qty = Range("D26")
        USD = Range("E26")
    Else
    End If
Next ws

 

[Kmahraz]

Thanks @chirayu , The sheet name matches in both the master and source
Master file

Source file

not sure why it does work for you and not for me

 

[chirayu]

hmmm try using the like statement then to see if that resolves the issue. if it doesn't then attach the files where its not working & i'll test it

 

[Kmahraz]

Thanks @chirayu

 

[chirayu]

replied on inbox message

 

[mdfreeman]

Is it a job for PowerQuery to normalize any data and then merge the data from each sheet together?

 

[Chihiro]

Is it a job for PowerQuery to normalize any data and then merge the data from each sheet together?

Sure, but a lot of people still use older Excel which does not support it. I use Office 365, but bunch of external clients use Excel 2003 still.