Okay will look tonight when I get home as I can't download at work

Morning Shane

The workbook you uploaded does not have the Macros i created in it - are you keeping both spreadsheet open while running the Macro? (i.e the orginal one and the one you downloaded from here?

If so depending on the selected workbook when you start eh code could well be executing on the workbook i uploaded.

Can you copy module 1 from the workbook i uploaded to your data workbook and try again?

Purfleet

Howdy Purfleet

You're right, I had actually saved the contents of module 1 to a module in the Personal Macro Workbook, then simply created a button on the toolbar and ran it from there. Should it not be the same thing running it that way?

Just now I opened your original file and copied the contents of module 1 to a new module in my file. I then added it as a button on the My Macros toolbar I have. When I ran it, the same thing.

I also tried double clicking the Confirmed Lays sheet and pasting the contents into that and running it that way, but still the same result. 

To your question, no, I do not normally have your original file open at he time. Each and every time I run it I am in my file and simply click a button on the toolbar to run the macro. Each time it fails on the same line. 

Would it help to have the file now resent with the macro saved in it to see if it makes a difference at your end? I still wonder if it not some quirk between Excel for Windows and Excel for Mac

cheers 

It may not make any difference if you added it to a Personal Macro workbook, it really depends if I used any reference to 'thisworkbook' or if I referred to them by name.
Personally, I would not add a Macro to the PMW unless it is a macro designed to work with any sheet - for example a macro that set page orientation and margins. I would always add the Macro the actual workbook

I will have another run through the code and maybe tighten up a few bits. Are all of the worksheets to be compared to each other (except Confirmed lays)?

Purfleet

https://1drv.ms/x/s!AomqbYFKl5Qeit9S_G2vA3X532YN0A?e=4Nj833

I have changed the file around a bit and made each worksheet loop and filter on each other, so it is a bit more efficient as we don’t need to have code for each sheet.

Just download the workbook and run the Timer or LayRunOrder (the only difference is the timer one reports the time taken to execute in VBA)

Only use the attached as the test file as i have deleted all the data from Confirmed Lays and also some other anomalies in the worksheets which will need fixing before you use the file in anger.

1) on some sheets there are hidden columns and rows - the macro unhides all of these each time it is run
2) the safe bets sheet has data in columns S & T from rows 335 to 916
3) the debut destroyer sheet has an extra column which means that the horse is in I and not H - Race type needs to be removed (or at least moved to the last column) so that all the headers for each sheet are the same.

When I ran the Macro it took about 22 seconds start to finish and copied over 10000 records to Confirmed lays.

I think the Macro is doing what you asked for, but you will need to test it extensively as I do not understand the data at all and I have also never used a Mac

Let us know how you get on

Purfleet

Hi

Thanks so much for your help. 

Any reason for a binary versus an xlsm file? Do you find it to be more efficient or faster that way? I'd not seen one before.

Can I assume that this For Each ws In ThisWorkbook.Worksheets addresses which sheets are to be compared? Unfortunately only the 3 I gave you were to fit the bill for this comparison. They are what is called Lay bets; it basically means you are wagering that something doesn't win, rather than betting it will win. The other sheets are the reverse. So I was planning on seeing once this one worked on the 3 sheets, then writing a new one to address the other 6 sheets.

I spotted this at another forum, where they have used the above code for all worksheets, but then excluded 3 of them by using this

Could something like this be used to exclude the other sheets on this particular occasion? If so, that would be great as I could simply exclude the ones 
which are Back selections for this one and then exclude the ones which are Lay selections for the next macro. I can see how to do the Case part but just 
wasn't sure what would go in Case Else. Just not 100% sure how to incorporate this to exclude the required sheets.

On checking it further, it would probably be this sort of statement in the Sub SetUp() just after this line For Each ws In ThisWorkbook.Worksheets

Am I on the right track with this?

Another anomaly is that all rows are copied, including the header rows from each sheet, so there are 9 header rows through the Confirmed Lays sheet. I tried changing the range from Range("A1:W" & currentWSLastRow) to A2, but it didn't like that one. There will be a way, I'm just not spotting it yet.  The RemoveDuplicates line should be handling it, but for some reason, it is not working at all, with duplicates all through the Confirmed Lays sheet. 

I also removed the autofit rows and columns so it doesn't adjust anything and removed the colours from the tabs. 

To avoid any of the confusion with some of the slightly different columns, could the range not have column names as exclusions? Race Value, Jockey Dis Place SR & Jockey Crs Place SR would be the only exclusions, thus making all the data the same size.

On thinking about it,  just as with the Case example above to exclude particular worksheets, could not Select Case be used to exclude particular column names from the selection procedure?

Thanks so much for all your effort. Just trying to fine tune it now so it works as I need, but it seems not far off.

Let me know your views on any of the above

Cheers

Any reason for a binary versus an xlsm file? Do you find it to be more efficient or faster that way? I'd not seen one before.

From what i have read xlsb files are more efficient especially for larger files and they are also significantly smaller. I was seeing if we could get the file under the 1gb limit to upload. (just found a great new way of sharing larger documents, and they auto expire - https://send.firefox.com/ - you can upload up to 2.5gb if you have a firefox account)

Can I assume that this For Each ws In ThisWorkbook.Worksheets addresses which sheets are to be compared? Unfortunately only the 3 I gave you were to fit the bill for this comparison. They are what is called Lay bets; it basically means you are wagering that something doesn't win, rather than betting it will win. The other sheets are the reverse. So I was planning on seeing once this one worked on the 3 sheets, then writing a new one to address the other 6 sheets.

I spotted this at another forum, where they have used the above code for all worksheets, but then excluded 3 of them by using this

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case exclude Case "Summary", "Employees", "Project Rules"
'do nothing
Case Else
'do your formatting
End Select
Next ws

Could something like this be used to exclude the other sheets on this particular occasion? If so, that would be great as I could simply exclude the ones

which are Back selections for this one and then exclude the ones which are Lay selections for the next macro. I can see how to do the Case part but just

wasn't sure what would go in Case Else. Just not 100% sure how to incorporate this to exclude the required sheets.

On checking it further, it would probably be this sort of statement in the Sub SetUp() just after this line For Each ws In ThisWorkbook.Worksheets

Select Case ws.CodeName (not 100% about what goes next to Select case, but took a guess)
Case "Safe Bets Lay", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
Case Else NOT SURE WHAT WOULD GO HERE
End Select

Am I on the right track with this?

I have never really had the need to use case, but from what i know it looks like you are on the right track

Another anomaly is that all rows are copied, including the header rows from each sheet, so there are 9 header rows through the Confirmed Lays sheet. I tried changing the range from Range("A1:W" & currentWSLastRow) to A2, but it didn't like that one. There will be a way, I'm just not spotting it yet.  The RemoveDuplicates line should be handling it, but for some reason, it is not working at all, with duplicates all through the Confirmed Lays sheet. 

I also removed the autofit rows and columns so it doesn't adjust anything and removed the colours from the tabs. 

To make the filter more efficient I thought it was better to leave the header rows in and remove with the remove duplicates - when I run it, it only left in the header from the worksheet that was different to the rest and maybe one extra at the top but again this is a fairly easy fix. Your call on the autofits but hidden data can cause unexpected results - it’s not doing anything now as it has been saved after unhiding but if you were to hide rows/columns again this would make sure they are visible - it is more of a best practice part than anything else.

Tabs were only coloured to make sure the macro was running on all sheets

To avoid any of the confusion with some of the slightly different columns, could the range not have column names as exclusions? Race Value, Jockey Dis Place SR & Jockey Crs Place SR would be the only exclusions, thus making all the data the same size.

On thinking about it, just as with the Case example above to exclude particular worksheets, could not Select Case be used to exclude particular column names from the selection procedure?

Autofilter needs columns to be exactly the same in my experience - feel free to test but i would make them the same with any extra columns at the end. If that column really HAD to be in H, then you could get the Macro to move it to the end, do the filter and then move it back for the file report. Again, this is up to you and your testing.

For the final results, if case works as you expect it to you would be left with one sub for Lays (3 sheets) and one sub for bets (7 sheets)?

Off to bed now to read up on VBA Case statements

Purfleet

To make the filter more efficient I thought it was better to leave the header rows in and remove with the remove duplicates

That was actually the point I was making - no duplicates are removed. Even if I run the original macro in the .xlsb file I downloaded, there are duplicates all through the sheet, so the FinishUp macro is not doing what it should. Any thoughts on what is stopping it from doing what it should?

cheers

Can you run it and upload the results so i can take a look? Maybe highlight a couple of the dups that you think should be removed

Purfleet

https://send.firefox.com/download/e6bf4367093027fb/#ObFpQ3qFQDQsz_iY_FScCg

Highlighted just a handful, but there are absolutely loads of them

cheers

Okay it makes more sense now. When i said unique I was comparing all columns so all columns must be a duplicate, but your data changes from column P (sometimes column C as well)

For example, Rows 5 and 7 - Blue Skyline in the 12:30 at newmarket

All of these are the same (a to o) - (picture 1)

But in pitcure 2 - these are different and therefore not unique in fact it looks like the data has move on one column in the second row which again, might be due to some inconsistencies in the columns headings in the original worksheet

This isn’t a huge problem if you only need to compare the first few columns as you can just change this line of code to and remove the columns you don’t want to compare for example if you only wanted to compare a to o (o is the 15th column) so delete all numbers after 15.

Range("a:x").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24), Header:=xlYes

I really would recommend looking at your sheets for inconsistencies as it will make anything you do going forward so much easier if it is all the same 

Purfleet

Thanks for the reply Purfleet. In the end, the only columns really required for a match are A, B & H. If those 3 match, then it is a dupe and those 3 columns are nothing more than Date, Time and Name, so there really can't be any inconsistencies.

The downside is changing the Array to just be 1, 2, 8 also made no difference. In fact, even trying to do a manual Remove Duplicates also had no effect, so really not sure what is going on. I even selected A, B and H individually and manually formatted them to make sure the data is the same. I also tried selecting each column, then copy and paste special and paste the data in as values, but not a single duplicate is found.

I am clearly looking at duplicates, yet Excel does not find them.

Ever experienced this before?

cheers

I just recorded a Macro to remove duplicates on one of the older versions we were playing with and it created the following code (i would remove the row numbers in the range to make sure all future rows are in included in subsequent runs)

Columns("A:X").Select
ActiveSheet.Range("$A$1:$X$46").RemoveDuplicates Columns:=Array(1, 2, 8), Header:=xlYes

This reduced the 'confirmed lays sheet' from 46 rows to 23.

As a suggestion, comment out or remove the remove duplicates line of code in your macro, run the macro through and then record a macro to remove the duplicates (i assume it is the same in Excel for Mac), hopefully it should have removed some of the rows and you will then have the code you need.

>Click record Macro
>Select all columns on worksheet
>Click Data tab & click remove duplicates
>Un-tick all the columns
>Tick Date, Time & Horse then ok

Don't forget that remove duplicates will only keep the first duplicate in a data-set, so you might need to sort before removing dups if you want a different duplicate.

Purfleet

Howdy Purfleet

What a struggle this has been and is still ongoing. The bottom line is the VBA compares all the sheets and it has proven very difficult to try and exclude the ones which are unnecessary. 

The only 3 sheets which are required to be compared are Safe Bets Lay (might be SBLay in the one you had), FA Lays 1 & FA Lays 2. I think it will be best to try and target only those 3 sheets.

I have tried this in the SetUp()

Sub SetUp()

Dim sheetsArray As Sheets

Set sheetsArray = ActiveWorkbook.Sheets(Array("Safe Bets Lay", "FA Lays 1", "FA Lays 2"))

Dim sheetObject As Worksheet

' change value of range 'a1' on each sheet from sheetsArray

For Each sheetObject In sheetsArray

'Do something

ws.Tab.Color = xlNone

If ws.FilterMode = True Then ws.ShowAllData

If ws.AutoFilterMode = True Then ws.AutoFilterMode = False

Next sheetObject

Worksheets.Add.Name = "Criteria"

Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1") End Sub

I think the CritWS all over the code still has it addressing all sheets. Should maybe each reference of CritWS be SheetsArray or something which, since that is now defined as only those 3 sheets.

cheers mate. Still unsure how to get it to only work on the 3 sheets

Ta

Hi shane

Okay mate, better to start again i thnik.

Can you resent the whoole file as is and we can work through it - i think we have the correct idea, it is just getting to work for your data.

Have you managed to tidy up the 3 sheets that need comparing? So all coulms are the same?

About to try and get back to the UK in the next few hours, so will check out over the weekend as we have no football or cricket to watch!