Issues copying data to another sheet

Hi,

Really having issues copying data to another sheet, i have tried copy/paste, copy dest, value = value, array etc. i have also tried all the manual calculation, nodisplayalerts and all the other usual things. When running on a non shared workbook with others, it works perfect no issues can run as many times as you wanted instantly, soon i share the workbook with others, thats when the fun begins, usually the first time it will run fine and fairly quick, the second time onwards, i could go and make myself a cup of tea/coffee before it completes. the status bar shows filling cells and the turtle with a pencil comes out writing out the cells. i have spent the last week pulling my hair out and searching all over the web and cannot find anything.

I have tried creating a new sheet incase there was an issue with the old still the same, tried disabling track history, on the local HDD, on the network, original i was copying from another workbook, so i have tried in the same workbook, all the same. below is the latest code im trying, i dont fully understand it as not used arrays before, but seems to work no shared. i will eventually have this putting data on multiple sheets, on a for loop somehow, but at the moment i need to get it working on one sheet. What am i missing ? seems like its not closing of previous run.

Please Login or Register  to view this content.

Sample data

Site Emp # FirstName LastName InTimestamp OutTimestamp ScheduledShiftID
1234 987654321 Joe Bloggs 26/09/2023 10:00 26/09/2023 16:00 11456259
1234 876543210 Jane Doe 29/09/2023 10:00 29/09/2023 16:00 11456261
1234 765432109 Captin America 03/10/2023 10:00 03/10/2023 16:00 11485315

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

You have made 10 posts so you CAN post the link in your next post, however if you read my post fully, you will see that I said you could tell us WHERE you had posted if you had fewer than 10 posts.

Please post the link in your response to this and read the information at the link I provided to understand why we ask you to do this (it's NOT against the rules, but not telling us about it is).

The most obvious potential code error comes here

Please Login or Register  to view this content.

The Format function expects a long integer or a "double" decimal value, not a string like "23/09/2023".

Yes, 45192 is the underlying number version of the date. However "Date1" is also a string but in the American date format.

This is being compared with a date/time stamp from your data table, which similarly has been coverted to an American style date format with no time element.

Please Login or Register  to view this content.

This works OK

Please Login or Register  to view this content.

"Shareworkbook" has been replaced by "co-authoring" so perhaps you should investgate this option

https://support.microsoft.com/en-us/...2-c1c59a628534

Hello @jynxy. What about optimization? Will there be a difference in how the code works?

Please Login or Register  to view this content.

UPDATE!
Try the following code to see how it works for you and how
Will the given code be performance-wise?

Please Login or Register  to view this content.

@MikeVol thanks, i have tried your suggestion and seems to work the best,

@JohnTopley ive tried to share and can only have one user at a time, so will have to look into this. also seems a pain to get to the excel app.