Subjective VBA error on copy sheets to new workbook

[rpinxt]

Sorry about the title I had no idea how to summarize my problem.

So I have a big macro in which call several smaller macros that copy out 1 or more tabs into a new workbook (and that then gets written away somewhere).

Now i have no problem with this macro but other users get errors and I don't see why.

The error is a Run-time error '9': Subscript out of range.
And this is the code:

Please Login or Register  to view this content.

Sheet3 is the correct sheet.
Also there are already 2 other small macro's (doing the same for other tabs) which gave no problems.
This one is the 3rd in sequence and gives us problems.

Did some research and saw something about the number of tabs a new added workbooks openend with.
I had that on 1 tab and my colleagues on 3, so we changed that but still this error.

Has anybody an idea??
Thanks!

[xlnitwit]

Hi,

Have you verified what is being returned from the SheetNameFromCodeName function? Additionally, there is no need for an array there since you are only copying one sheet.

[rpinxt]

You are correct xlnitwit. And there is only 1 sheet to copy in this case.

However as said 2 occasions of the same already run before this one.
And both those new workbooks also only have 1 sheet to be copied and thus end up as a 1 sheet new workbook.

That is why I do not understand this.
I would think it must have something to do with settings because I can run it without problems.

But occassionaly it also crashes on me....but in later attempts it then just runs fine....
And for colleagues it always crashes.

Makes no sense to me, but I'm sure if I ever find out the problem it will be logical

[rpinxt]

And yes it returns a ""

[xlnitwit]

Then the problem is most likely in SheetNameFromCodeName, but this code should be testing for "" as a return before it tries to copy that sheet.

[TMS]

You use:

Please Login or Register  to view this content.

However, the Active Workbook is not necessarily the workbook with the code, "ThisWorkbook". It may be that your colleagues have a different workbook active when the code is run.

It would be useful to upload a sample workbook with all the code.

This worked for me:

Please Login or Register  to view this content.

Note that the SheetNameFromCodeName function that I drafted to test this uses the worksheet rather than a text string.

[rpinxt]

Thanks TMS.
But not sure what worked for you?

And an example workbook would not really be an option I'm afraid

Maybe I can elaborate a bit more what happens (maybe someone can see why the correct activeworkbook would not be the one that is active a that time).

ps: is there no otherway then using activeworkbook?

Code:

This is from the general Macro:

Please Login or Register  to view this content.

Then these first 3 calls :

Please Login or Register  to view this content.

As you see there is yet another Call in those. But before 2 things needed to happen to these seperate workbooks so there were 2 Calls in (could now be merged into the first call, but first I need to get it to work)

Then the last Call where the error happens only on the 3rd instance for my colleagues :

Please Login or Register  to view this content.

[xlnitwit]

Perhaps the correct workbook is active but the code name of the sheet is not what you expect.

[rpinxt]

Well I would think so.
First Sheet1 and Sheet2 without problems so then I would expect Sheet3 also to work.
As you can see in the image thats the Finance one and thats what I refeence with Sheet3

2017-03-22_14-20-37.png

[xlnitwit]

Are you checking that when the code is failing, or when it's working on your machine?

[rpinxt]

I checked that in debug mode xlnitwit.

It returned a "", I thought that would be wrong but I understand now that would be correct?

[xlnitwit]

You have not as yet provided the code for your SheetNameFromCodeName function, but "" would appear to be incorrect assuming the correct workbook is active. This seems like a very convoluted method of copying some sheets.

[rpinxt]

Ah right :

Please Login or Register  to view this content.

Well to give you the big line.

Macro opens a Data workbook (from current month).
Macro opens Template.
Template has links to Data workbook (always same name).
Template gets update.
Then Macro devides these 22 sheets in smaller workbooks and writes to folder and makes sure the links are broken.

[xlnitwit]

I think you need to do some debugging, such as outputting wb.name in your codename function so that you can verify which is the active workbook.