VBA Export selection of a Dynamic Array (FILTER) to new workbook

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
Good afternoon,

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.

Is this possible?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

WSBirch

New Member
Joined
Apr 10, 2018
Messages
35
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;
ABCDEFGHI
1SHIPFROMMETHODID#CTNWEIGHTQTYWHODATEHOW

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,923
Messages
5,621,625
Members
415,848
Latest member
LCC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top