Help with subscript out of range error

Hi all,

I have the following code to create copies of my 4 template sheets to create a sheet for each month. It seems when the loop gets around to creating a copy of the template for the second time it doesn't like it. I can't figure out why or a solution to work around it. So when it gets to "Sheets("Jan" & vName).Copy after:=Sheets(i2)" to create the first March sheet, I get subscript out of range. Any ideas?

Please Login or Register  to view this content.

Thanks!

Not sure if this will fix your problem, but I can tell you that Sheet.Visible will never be True, as it is not a Boolean. Try replacing with this and see how you go:

Please Login or Register  to view this content.

Questions that arise: where is the variable i2 defined (dimmed)? Where does it have its value set? How do you think the function visible_sheets works?

Do you have global variables for i and i2? Are they set in another module/subroutine? A function returns a value through the function name, as a general rule.

@mgs73 - When calling the function, I'm able to get the correct number of visible sheets, so that is not the problem.

@InvisibleMan - I've changed a fair amount of the code at this point so it's tough to answer your questions.

I think a better question for both of you would be once I have the function visible_sheets, how do I return that number in my subroutine? The portion "after:= Sheets(i2)" doesn't work because I can't seem to return the value from visible_sheets.

Please Login or Register  to view this content.

To be fair, I don't really understand the relevance of visible sheets. You could just put the new sheet after Sheets(Sheets.Count)

You have to define i2 or don't use it.

Maybe you want the below

Please Login or Register  to view this content.

then use it like this

Please Login or Register  to view this content.

Can you upload a workbook? It is likely an easy fix, but too difficult to guess what it might be.

+1 to everything InvisibleMan said. Do you have 'Option Explicit' at the top of your code?