Looking for Last worksheet in a workbook

[realniceguy5000]

Hi Everyone,

I was hoping someone could help me out with this script? I know it a bit rough but most of it works ok.(still working on it) I just need to change a bit of the script so it does not pull from the worksheet with the name 8-11-08 or aug 11. The information im looking to pull will always be in the last worksheets See the hi lighted area below in red is the problem.

One last thing if you take a look at my call script in the last part of this post is there an easier way to do what I'm doing. I know it works just seems like a lot of mess...



As Always Thanks for your help... Mike

Main Script

Please Login or Register  to view this content.

Call Scripts

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Assuming you mean the last worksheet is the one furthest to the right, and not necessarily the last one introduced, then you could use the following bit of code to derive the sheet name as a string, and build this string into your existing code in place of the hard coded names.

Please Login or Register  to view this content.

As for the called procedure, I wonder whether you need it. If you Dim'd an an array in your main procedure and then populated your array as follows

Please Login or Register  to view this content.

You could then use a For x = 1 to ? ... Next loop, which is set to however many column reference items there are in the array, and then use the x counter to pick the first, second etc value from the array and use that .
So instead of your

Please Login or Register  to view this content.

you would have

Please Login or Register  to view this content.

HTH

[dominicb]

Good evening realniceguy5000

This will return the name of the last worksheet :

Please Login or Register  to view this content.

In general you don't have to select cells to work on them, so your code

Please Login or Register  to view this content.

could actually be replaced by :

Please Login or Register  to view this content.

You'll find code runs far quicker this way.
If the project was mine, I'd probably keep your seperate find routines in with the ain routine, and I haven't looked too closely at it but you could probably do all your finds with just one routine that loops through each of the letters you need to find.

HTH

DominicB

[realniceguy5000]

Much Thanks for your input. Since this is rather complicated as least for me. I will try to address just the problems I'm having with the start of the project. I will work on the call scripts later.

This project has 3 workbooks A,B And C Workbook A is all the totals from B and C So to start easier lets just work with A and B the formula below needs to always look at the last worksheet in workbook B sheets.count

Here is the problem with this line. It is looking at the wrong workbook. I need it to look at my B Workbook not My A Workbook How can I get this completed?

Please Login or Register  to view this content.

Next for some reason I am getting a application defined runtime error at this line.I'm sure it has something to do with the part in Red

Please Login or Register  to view this content.

Once again I really appriciate all the help!!! Mike

All By the way here is the compete code

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

If there's more than workbook open and there's any danger of getting the two confused, as in this case, then you need to explicitly include the name of the workbook, (either by referring to it's name directly or Dimming and setting a wb variable when you first open it, or as you've done in this case creating a string variable) in the code. So instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

Incidentally I'd be inclined with your Dim statements to declare their type. e.g.

Please Login or Register  to view this content.

or if you want to follow good programming practice, include a reference to its type in its name. e.g.

Please Login or Register  to view this content.

HTH

[realniceguy5000]

Thanks for the tips... Much Appriciated.

I still having some issues with this script. As you can see I think I adjusted my script per your advice, However now I'm getting a "subscript out of range error" at this point in my script

Please Login or Register  to view this content.

Here is the compete Script... Thank You again for taking the time to help me out.

Mike

Please Login or Register  to view this content.

[Richard Buttrey]

Hello Mike,

I think the problem is that you've included the path when assigning the 'B' string variable.

Try instead just:

Please Login or Register  to view this content.

Rgds

[realniceguy5000]

Hi Richard,

And Thank You...

I'm not sure what i'm doing wrong. I tried to switch it around however for some reason I'm still getting that same error.

Anymore ideas to try? Here is the new code... I switch the letters from B to V just for testing.

Please Login or Register  to view this content.

[Richard Buttrey]

Sorry, my fault. I shouldn't have included the ".xls" in the assignment to the variable.

Just use

Please Login or Register  to view this content.

[realniceguy5000]

Thanks Richard...
No Worries Everyone makes mistakes...I make plenty of them...
I guess you figured out what I'm going to say next. Yes another problem. So I changed the code again, But I have another error.
Runtime 1004
Method'Sheets' of object'_global failed

Please Login or Register  to view this content.

I then moved this code statement above down past where the workbooks are opened which then gave me the
Runtime error 9 Subscript out of range again.

I did notice that it is looking at the correct workbook it also is counting how many sheets are in that workbook. However it appears to have a problem with the .name part

Would it matter if the name of the sheet is a date?

Anymore More Advice? Many Many Thanks...We must be getting closer...Mike

Once again here is the entire code

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Obvious question I know, but why are you opening workbooks A & B, and not A & V ?

For

Please Login or Register  to view this content.

to work, your 'Varsity Production' file must be open in memory.

Rgds