Is it possible to make this code run on multiple sheets?

[maco20]

Here is a link to my original post:- http://www.excelforum.com/showthread.php?t=1169539

Although the above post solved my initial problem I soon realized that I had to change my workbook layout in order to make it manageable and easier to use.
Where as the previous version had 1 sheet/tab for all staff I had to change it so I now have a number of tabs across the bottom which relates to the first letter of a persons surname

Example:
All staff with the surname beginning with A are placed in the tab labeled "A"
All staff with the surname beginning with B are placed in the tab labeled "B" and so on....

What I want to know is, is it possible to change the code given to me previously in order for it to work on all pages as at the moment it only works on tab A.
I have tried simply copying the code into tab B and changing the variables at the top and as far as I can tell it hasnt worked unless i've done something wrong.

Code being used in tab A.

Please Login or Register  to view this content.

Up to date copy of a slimmed down version of the workbook below.

[bulina2k]

How is this code triggered ?
Is there a button ? Or event?

[maco20]

Sorry I forgot to mention that. Its currently triggered using Ctrl + x

[bulina2k]

Actually this is your problem. Both of the codes in your sample file gets triggered using Ctr+X. You should find another triggering event or change the combination for each code. (every code works fine )

[maco20]

Would it be possible to make the code run on every page using 1 trigger??

[bulina2k]

Can you please give me some name examples?
is it Name Surname - goes to sheet S ?
or is it Surname Name?
I'm trying to set the N something like

Please Login or Register  to view this content.

[maco20]

It's Surname Name goes to sheet S

Example
Smith John will got to sheet S

[bulina2k]

perfect! my code should work fine:

make this replacements:

Please Login or Register  to view this content.

[maco20]

Sorry for sounding dumb but does the code stay in sheet A? as I have made the changes you mentioned but now nothing is happening.

Also does the below variable stay or go?

Please Login or Register  to view this content.

[bulina2k]

I have tried the code and it works fine (there was a short error cause in Shorts and Overs tabs use to be 'Thur' and in the alphabet tabs just THU)

I'm uploading your file with the modifications.

[maco20]

Thanks for your help your copy works.

The only problem im getting now is that if the shorts and overs pages are blank and you accidentally run the code which I did it throws a error 400 up and the script no longer works at all.

[maco20]

Looking for a bit more help... The end is in sight I believe haha...

Ive sorted the error 400 that I mentioned above however the problem I have is that when I change the date im getting another error which is

"Object variable or With block variable not set."

This is also happening in the sample file uploaded by bulina26 above.

Any help to finish this would be greatly appreciated.

Thanks

[jaslake]

Hi maco20

Please attach the offending Workbook (YOUR Workbook that exhibits the error).

[bulina2k]

hi there!
the problem is this line:

Please Login or Register  to view this content.

this line is looking to find the column to write in, searching for the weekday (Mon Tue etc..) in the N sheet just a row above which is good for the first date but wrong for the any other.
So in order to correct it we will use the 8th row (the table header)

You should replace myDateRow - 1 with 8

Please Login or Register  to view this content.

There are 4 replacements to be done (2 for the Shorts and 2 for the Overs)
Please tell me if you find any other problem.
(Sorry for keep you waiting)

[maco20]

Thats fantastic everything as far as I can tell from my tests today works exactly as it should.
I didnt mind waiting as your help has and will save me plenty of time in the future.

I would also like to thank jaslake who provided me with the initial code...

Maco

[maco20]

It looks like I spoke to soon.

The error 400 is back... If 1 or more names does not have any data on the "Shorts" page the error is displayed.
If all names have at least 1 number next to it in the "Shorts" page then there is no problem and the error isnt shown.
I have tried leaving one row blank but putting a number in the overs page but the error is still shown so it has to be something to do with the shorts page...

Any ideas?

[bulina2k]

yes, replace (2 replacements)

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[maco20]

Another one bites the dust :-)