VBA code to copy and paste between sheets based on the selection criteria being a date

mps80y

New Member
Joined
Apr 3, 2019
Messages
22
Hi All,

I have a spreadsheet that has several sheets;

Sheet 'Calc_Sheet' contains a value in cell E1 which lists the last date of the previous month in the format dd/mm/yyyy and is formatted as a Date field - *dd/mm/yyyy.

I have another sheet 'Previous Month' that has all the data of bookings made. Column C in that sheet also has a date in the format dd/mm/yyyy and is also formatted as a Date field - *dd/mm/yyyy

I need to copy all the rows from the 'Previous Month' sheet, that match the date in cell E1 in sheet 'Calc_Sheet' and copy them to the end of a third sheet 'Current Month'.

Another member has helped with the code and its working fine if my computers regional settings are configured to mm/dd/yyyy but the code doesn't work if i change my regional settings back to dd/mm/yyyy (as I'm Australian based).

Any suggestions on how to get around this?

A link to the file is here

TIA :)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
It appears that the issue is with the way cDate handles the date - it only recognises the format mm-dd-yyyy, anyone have any ideas on how to change it to accept dd-mm-yyyy

Revised file is here
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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
Back
Top