Copy rows that meet weekday criteria to different worksheets

[webwyzard]

Hi all

I am looking for the quickest method to copy rows of data that have a date in the first column.

The idea is to copy (in date order) any weekday that is a : -

Monday into the Monday worksheet
Tuesday, Wednesday and thursday into the Midweek worksheet.
Friday into the Friday worksheet.

I have potentially 50 to 100 thousand rows with data as below.

20/04/2020 06:35 0.63429 0.63429 0.6339 0.63399 40
20/04/2020 06:40 0.63402 0.63413 0.63386 0.63399 40
20/04/2020 06:45 0.63398 0.63409 0.63385 0.63385 13
20/04/2020 06:50 0.63379 0.63396 0.63379 0.63387 28
20/04/2020 06:55 0.63392 0.63424 0.6339 0.63421 25

Is it best to add a row with an =Weekday(a1) into a new column and then loop through the rows to copy each to the respective sheet.

Or to do a row by row check on the date and copy during the loop?

Or is there a quicker way? Perhaps using filter?

Thanks

Neil

[maniacb]

I recommend adding a weekday column and then using autofilter to select all the common days.

[maniacb]

Here is some code for your situation.

Please Login or Register  to view this content.

[maniacb]

This iteration address the "midweek" requirement

Please Login or Register  to view this content.

[webwyzard]

Hi Maniacb.

This works wonderfully thank you so much.

It has left me with a slight problem in that i had forgotten that i had formatted the A1 column to the date format dd/mm/yyyy from a text date i.e. 2020.04.23 format.

Your code then uses my formatted date to do the work.

To do this i used

Please Login or Register  to view this content.

Is there a way to use the text date "as is" in your code.

I have tried incorporatiing it but date formatting in excel is a bit of a minefield to me.

Ultimately i will need to leave the A column as is and delete the added columns. I have coded the column deletes in your "with - end with" loop (Columns("H:J").EntireColumn.Delete)

The program the csv file is used in can only have dates of type yyyy.mm.dd as shown.

BTW on checking the format of the column a as received it comes up as General if that helps.

Thanks for your help.

Regards

Neil

[maniacb]

Can you post a snippet of your original file so we can get the column designations correct?

[webwyzard]

The original is a csv, sample attached.

I also added,

Dim sht As String
sht = ActiveWorkbook.Worksheets(1).Name

To use the current workbook / first worksheet name in your code.

I am curently writing code to save each worksheet as the original filename and a suffix dependent on each of the worksheets created.

Thanks

Neil

[maniacb]

Alrighty, this code worked for me, But the file needs a title line I typed in manually

Please Login or Register  to view this content.

[webwyzard]

Hi maniacb this worked brilliantly but the date column "a" changed it's format to dd/mm/yyyy.

It may be the way I explained it, but i need it to remain (or change back to) yyyy.mm.dd so it can be used in the program it came from.

Could it be a UK default date format problem?

Thanks

Neil

[maniacb]

Oops, I changed it. We can change it back. I’ll update the code

[webwyzard]

After studying the code more I managed to get it to work by changing both the formatting parts e.g. .NumberFormat = "mm/dd/yyyy" to .NumberFormat = "yyyy.mm.dd" and the same for the Format(Range("A" & i).Value, "mm-dd-yyyy")

Thanks
Neil

[maniacb]

So You all good?

[webwyzard]

Yes i am all good. Though my other half might disagree

thank you again.

regards

Neil