Splitting Data to separate files - does not work with "date" as the filter

I am looking to run the attached file but to split the sheets based on the "Date" field, which seems to cause issues.

This is a weekly file that would have to be run, so rather than focusing on the date (because in reality the date is not important, it is only used as a split filter), I was thinking that if there was a way to essentially Take the unique dates that appear,
sort them from smallest to largest, and then replace the smallest with a "1", next "2", etc... up to 5.

Or even with a name? Ship Date 1 will always be a thursday, 2 a friday, 3 Monday, 4 Tuesday, 5, wednesday.

I have found that if i manually put in a number or type in a day name that the split works fine... even if i convert the date to a number it works fine... it is only when it is in date format that it does not work.

How about in a blank column
=WEEKDAY(O3,14)
Change O3 to where the date is.
It will return 1 for Thu 2 for Fri, 5 for Mon etc.

Originally Posted by JLGWhiz

I assume you are referring to the SaveAs Statement that is giving you the probelm. To use the Date there you would need to use the Format funtion, eg. Format(SaveDate, "mm/dd/yyyy") and concatenate it into the path string.

I tried changing the Saveas statement to

Please Login or Register  to view this content.

And I get an error when i run the code.

Ideally, I would like to keep this in VBA and not need extra formulas in the sheet if possible.

Ideally I would like to just be able to have the file names saved as "Ship date 1", Ship date 2", etc... and not have the date in the file names whatsoever.

Originally Posted by alansidman

How about adding a column to your table and putting this formula in V5 and copying down then using column V to filter and split your data

=IF(WEEKDAY([@ShipDate])=5,1,IF(WEEKDAY([@ShipDate])=6,2,IF(WEEKDAY([@ShipDate])=2,3,IF(WEEKDAY([@ShipDate])=3,4,5))))

This coding works... ideally I would like it to be a behind the scenes done in VBA thing.. .but I can work with this!

Thank you!

Okay, so if I have a formula in the cell for the column that has the "=IF(WEEKDAY([@ShipDate])=5,1,IF(WEEKDAY([@ShipDate])=6,2,IF(WEEKDAY([@ShipDate])=2,3,IF(WEEKDAY([@ShipDate])=3,4,5))))" in it, then the macro will not run. If I convert the formula results to text, then the macro runs.

So in the end, this is not working still.

Any other thoughts for what I can try?

Possibly...

Please Login or Register  to view this content.

Originally Posted by dangelor

Possibly...

Please Login or Register  to view this content.

Now that is perfect! One final question that I did not think of, and I will leave you alone.

For the folder that it saves to, if I wanted to have it be "C:\Users\markf\Desktop\Test\Today's Date" so that each of the created files just went into a folder with the file creation date on it... would that be possible? Is it also possible to have a copy of the original file that the data is pulled from save in that folder as well? (that way if 1 year from now we had to reference the data we can easily find it and see the source data)

I was able to figure out the creating a directory name + date... and KINDA SORTA have the saving of the original data file as well... but i think there is a better way to do the save. Please take a look at the below code and let me know your thoughts.

Please Login or Register  to view this content.

As always, if it ain't broke, and it works, don't fix it. Could it be more 'elegant'? Probably... play with a copy and see what you can do and not do. Enjoy the day!

Grrr.... Gotta love when things do not work exactly how you want them to.

So I need to instead of having the files have a save name of a Date, the files need to save as literally the words "ShipDay1" "ShipDay2"... "ShipDay5"

How do i pull that 1,2,3,4,5 into the "save as" info?

I incorporated Fluff13's formula... Not Tested!

Please Login or Register  to view this content.

Thank you for your help!

One last thing that I just noticed

I think that after the Autofilter 18 is put on that it needs to sort by that column?

In looking at the files generated it showed "ShipDay5" as having the earliest ship date... Shipday1 had the second earliest....

I think the code is pulling the date by the order in which a date appears in the column, not the order the date comes chronologically, if that makes sense

This tested OK on my system. The filename matched the shipdate day of the month - ShipDate2 = 8/2, ShipDate7 = 8/7, etc.

Please Login or Register  to view this content.