Creating multiple files from an excel filter

[robmaag]

b. Take a spreadsheet that has in Column D a name for an entity: (for example, call it 'United Airlines')

b. Filter the data in spreadsheet by Column D (called United Airlines). Note there will be 56 of these queries...one after the other with diffrent names...

c. Delete all other data in Column D that is not United Airlines

d. Save the results into an excel file

e. Place the file in its own folder


Any thoughts?

Thanks

 

[robmaag]

The file is being used by another progarm

The workbook you are tyring to save has the same name as a currently open workbook.

These are the new errors..

 

[Domenic]

Actually, since the 31 character limit refers to the sheet name, I should have offered the following...

filteredSheet.Name = Left(sCleanFileName(criteriaCell.Value), 31)

The character limit for the path and filename is 218.

 

[SpillerBD]

Another way would be via Pivot Tables and a feature available wit the Page Filters. See Excel Pivot Table: Outputting detail on multiple tabs - Microsoft Community
Then it would just be a matter of sending those new sheets to a New files(s).
Code exits in Jelen's Excel 2013 VBA and Macros Chapter 12

 

[robmaag]

Dominic or ALL,

Thank you for your help on this small project. I believe I found the problem.

The problem has to do with the uniqueness of the file name.

I think the above code is hanging on the following filter criteria:

MCKESSON

MCKESSON - VALLEY CARE IPA

MCKESSON - VALLEY CARE SELECT IPA

<tbody>
</tbody><colgroup><col></colgroup>

For some reason the truncation is thinking it is a duplicate..Perhaps we can place a number followed by an underline in front of each filter criteria generated?

Your thoughts...Please help ....

 

[Domenic]

How about adding a date and time stamp to each filename to ensure uniqueness? If so, try...

saveName = sCleanFileName(criteriacell.Value) & " " & Format(Now, "yyyy-mm-dd hh-mm-ss")

If the file extension already exists, try...

saveName = Replace(sCleanFileName(criteriacell.Value), ".xls", " " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".xls")

Change the file extension, accordingly.

Hope this helps!