retrieve colums from external worksheet

[chriscla]

Hello

Each month I get from our warehouse dept a worksheet with the following data:
customer number, customer name, sales rep, budget (EUR) and then per month actual sales (EUR), actual sales (pieces) and monthly budget (EUR).
The file is updated once a month (getting a new file name: sales 17 02.xlsx, sales 17 03.xlsx, sales 17 04.xlsx, ...), meaning three columns are added every month. Also the number of rows may change, depending on the growing number of customers.

After filtering on sales rep, I would like to copy the following columns to another worksheet (SalesResult.xlsm):
customer number, customer name, actual sales per month (column heading=Act 01/17, Act 02/17, Act 03/17, ...)

I attached a dummy file to show the data I receive.

Any help is very much appreciated.
Chris

[MatrixMan]

Hi Chris. Does the copied data go to another worksheet as you say, or workbook as implied by naming it SalesResult.xlsm? If the latter, is this an existing workbook the data is to be written to (i.e. updated) or is it to be created each time. If the latter, why is it a macro workbook and not an xlsx workbook?

Do you have control over the workbook you get from the warehouse so that you can give them a new version with code? Or should this be written as a new "control" workbook to reference the existing workbook?

[chriscla]

Hi Matrixman

Answers to your questions:
The copied data goes to another workbook SalesResult.xlsm.
This is an existing workbook that needs to be updated every month.
I'm using macros in this SalesResult.xlsm workbook to get charts.
I have no control over the workbook form the warehouse. The data for SalesResult.xlsm are to copied out of the warehouse workbook.

Hope this helps.
Chris

[MatrixMan]

OK - understood. In that case, you've provided a copy of what the warehouse sends (test.xlsx) and I need a copy of SalesResult.xlsm as well (since that's where the code goes and I need to see how the data is laid out).

[chriscla]

Dummy file SalesResult attached.

[MatrixMan]

Thanks Chris - one more question ...

If I filter on S1, the summary data goes to salesresult ... if I then filter on S2, does that data get appended to the S1 data in salesresult or does it overwrite it? i.e. does salesresult hold more than one summary report at a time or just the one?

[MatrixMan]

Hi Chris. Attached are the two workbooks. Your warehouse "source" file and your macro-enabled "control" file. The control file establishes a monitor in a new sheet "FilterMonitor" that changes whenever the filter in the source file is changed, which in turn triggers the "calculate" event in the control file. See files attached.

There are a few safeguards built in but the main one is to use the button in the FilterMonitor sheet to open your source file; this is to avoid multiple windows and the code not recognising that the source file is actually open.

Code in the Blad1 sheet is:

Please Login or Register  to view this content.

And code in the FilterMonitor sheet is:

Please Login or Register  to view this content.

[chriscla]

Hi Matrixman

Thanks for your reply and solution. It does in fact what I was looking for.
Just one thing: the name of the workbook from which I retrieve my data changes every month: i.e. February is 'sales 17 02.xlsx', March will be 'sales 17 03.xlsx', April will be 'sales 17 04.xlsx', etc.
I've tried opening a source file with a different name than the one you put in the GetData macro

Please Login or Register  to view this content.

but then I get a pop up saying 'The workbook named chriscla - sourcefile1.xlsx is not open' and there are no results displayed in the chriscla - report data triggered... workbook.
Is there a way to get the data from workbooks with changing filenames and maybe also without having to open them?

Best regards
Chris

[chriscla]

The filter on the sales rep should indeed replace/overwrite the results in SalesResult, but I noticed this is happening in the solution you sent.

Thanks Chris - one more question ...

If I filter on S1, the summary data goes to salesresult ... if I then filter on S2, does that data get appended to the S1 data in salesresult or does it overwrite it? i.e. does salesresult hold more than one summary report at a time or just the one?

[MatrixMan]

The filter on the sales rep should indeed replace/overwrite the results in SalesResult, but I noticed this is happening in the solution you sent.

Yep - lucky guess

[MatrixMan]

Hi Matrixman

Thanks for your reply and solution. It does in fact what I was looking for.
Just one thing: the name of the workbook from which I retrieve my data changes every month: i.e. February is 'sales 17 02.xlsx', March will be 'sales 17 03.xlsx', April will be 'sales 17 04.xlsx', etc.
I've tried opening a source file with a different name than the one you put in the GetData macro

Please Login or Register  to view this content.

but then I get a pop up saying 'The workbook named chriscla - sourcefile1.xlsx is not open' and there are no results displayed in the chriscla - report data triggered... workbook.
Is there a way to get the data from workbooks with changing filenames and maybe also without having to open them?

Best regards
Chris

Does the sheet name and / or position change as well? i.e. is it either always called "blad1" or is it always the first sheet in the file?

[MatrixMan]

New file attached to accept any source file.
This is in the FilterMonitor sheet object:

Please Login or Register  to view this content.

This is in the Workbook object:

Please Login or Register  to view this content.

And this is the main code in the Blad1 sheet object:

Please Login or Register  to view this content.

[chriscla]

Hi Matrixman

Sorry for the late reply but I was out of the office for a couple of days.
I tried the last file you sent, but I get an 'error 1004: error defined by application or object' pop-up.
I attached a screen shot of the code.
I've tried it on the test.xlsm file and on the 'real' file but both give the error.

Chris

[MatrixMan]

Hi Chris - sorry about the delay; to be honest I'm not 100% sure why that's happening but I do get that error sometimes when calling a routine outside a module; it also seems to happen sometimes but not always so it's an odd one. In any case, I've modified it so that the code sits in a module and the error has disappeared. I also found a little bug in the logic as well related to my reuse of the wkb parameter that I've corrected. Files reattached also.

So now there is nothing in the Sheet1 object and this is in the FilterMonitor sheet:

Please Login or Register  to view this content.

This is in the ThisWorkbook object:

Please Login or Register  to view this content.

And this is in a new Module:

Please Login or Register  to view this content.

[chriscla]

Thanks for the new file. When trying I noticed that when I change the sales rep filter in the sourcefile, I get a Error 438 pop up.
When clicking the end button and reclicking the open source file button in the report data file, I get the correct results.
Maybe there's a solution for this, if not, thanks for all your efforts.
The max would be to be able to choose the sales rep in the report data file, but maybe I'm asking to much now.

Chris

[MatrixMan]

Oops - my bad. I forgot to change the call in the Worksheet_Calculate event after moving the code to the new module - sorry.
Just replace the call in that routine from this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[chriscla]

Thanks a lot. Works like a charm now.