Extracting Data to external workbook

sandrafullard

New member
Joined
Aug 18, 2018
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2010
I have a workbook with data in month worksheets. I want to be able to have an external workbook that will allow me to choose a month (say by a dropdown) and then show the data. The data needs to be filtered to give me the results I need. For instance, I have a column that I want data from that needs to be filtered to show all of a certain interview type (column O), then column D filtered next to show all of those that have missed their interview. I need this information to show without gaps in rows in my new workbook. I have included workbook samples. Please help!!
 

Attachments

  • Target Workbook.xlsx
    42.8 KB · Views: 16
  • Data Source Workbook.xlsx
    35.5 KB · Views: 12
Hi and welcome
what have you already tried? Where did it go wrong?
 
I looked in to doing if statements because the tabs and data will not change but I couldn't figure out how to do the filter and bring the data over without gaps in the rows. I also thought about running a macro to filter then copy the data over but didn't know how to have it choose the appropriate month. I can't alter or change anything in the original workbook other than a filter to get the data then unfilter to get it back to the original state.
 
In the attached, you'll see a table on the sheet, your dropdown in cell A2, and a button.
The button runs a small one-line macro blah:
Code:
Sub blah()
    ActiveSheet.Range("A4").PivotTable.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="C:\Users\Public\AppData\Local\Temp\[Data Source Workbook.xlsx]" & ActiveSheet.Range("A2").Value & "!R1C1:R999C34")
End Sub
You will have to manually edit the code to point at the right file name and path, this bit:
SourceData:="C:\Users\Public\AppData\Local\Temp\[Data Source Workbook.xlsx]"

As it stands it uses the range A1:AH999 (the bit which reads: R1C1:R999C34). If row 999 is not far enough down then change the 999 bit to a bigger number.

There are no checks to ensure the file exists, the sheet exists etc. so it's almost guaranteed to throw an error when you first try it. Just click End in the dialogue box that pops up and correct the file name and path in the code.

What it does is alter the source data for the table to the file name and path in the code and the sheet name comes from your cell A2. Choosing a month/sheet name in cell A2 which doesn't exist in the source workbook will cause an error.

This is a basic solution and could be vastly improved, perhaps with Power Query/Power Pivot.

Anyway, just a start.
 

Attachments

  • ExcelGuru9311Target Workbook.xlsm
    25.5 KB · Views: 10
Last edited:
Back
Top