Good morning,
Is there a way to do the below?
Is there anyway, using VBA, to export a particular range of data that's been populated with a dynamic filter from my current workbook to a new workbook? My current workbook has 5 sheets. I want to export a range of data from Sheet1 to its own workbook.
In Sheet1, I have Columns A-H with data. I want to export Columns A-F, ignoring G and H, to a new workbook all by itself as raw data. The concern I have is that the dynamic array is all built from one cell, Cell A2, which then populates the entire table from A2 to H1500. I want to export Range A1:F1500 from Sheet1 to it's own workbook - but not just simply a "Copy/Paste" because there's not actually data in any cell other than in A2.
If not, I'm wondering if there's a better way to accomplish what I'm trying to do then? I have a MS Access query tables worth of data that needs refreshed before using the workbook, then I need see certain filtered columns of information, but at the end, I need to export that data as an unformatted excel sheet by itself. The table has about 30 columns and it's required to show only 6 columns before exporting - using 4 different filter parameters that change based on cells values on a different sheet, we'll call "SheetB".
For simplicity, I have a table of data with these column headers;
| A | B | C | D | E | F | G | H | I |
|---|
| 1 | SHIPFROM | METHOD | ID# | CTN | WEIGHT | QTY | WHO | DATE | HOW |
From the table of information I have, all those columns, I need to filter out everything and show me just these columns only, then export it as its own single sheet workbook.xls;
SHIPFROM, METHOD, ID#, CTN, WEIGHT, QTY
Of all the data from the above table, I need to filter it based on the "SHIPFROM", "WHO", "DATE", and "HOW" columns. I need to filter these columns based on the value of a cell from SheetB.
A. Data in "SHIPFROM" can be "
MO" or "
PA"
B. Data in "WHO" can be "
1", "
2", "
3", or "
4"
C. Data in "DATE" can be any date from 2 weeks ago to 1 week from now
D. Data in "HOW" is either "
PPD" or "
COL"
Therefore, I have 4 cells on SheetB that uses data validation lists that you just select from the drop down menu created.
For example, from the drop down menus, I'll select A.
PA, B.
2, C.
2/10/2021, D.
COL and obviously I expect to see a table of information that's been filtered using those parameters. The value selected from those lists is different every time we need to export the sheet. I know I can easily do this manually if I want the same filter every time by just using Excel's table filters, but they need to change each time based on what we need at that time, so I don't really know how to filter the table based on different parameters each time and then export the 6 columns of data I need into its own single sheet workbook.xls. Ideally, we'd like the sheet that's exported to have a filename equal to the "If SHIPFROM=PA, 123, 456" & "TODAY()" & "TIME"
The total process of what I need to have happen is: Enter the 4 parameters for filtering, have the table sorted, and then export the sheet as unformatted data as a particular filename - all on the click of a button.
I'm sorry if I'm not very clear. All help is greatly appreciated, thank you!
