Runtime Error 9 "Subscript Out of Range" Assistance

[hg_macro_support]

Hi!

I am creating a macro and in one part of it I am attempting to move all of the sheets from one workbook to another workbook. When I attempt the macro, it comes up with an error 9 "subscript out of range". Any idea how I could fix this?

For further detail, I am deleting sheets in workbook 1, then moving all of the sheets from workbook 2 over to workbook 1 and leaving a copy of the sheets in workbook 2.

Here is where VBA says the error is coming from:

   Sheets(Array("DAR002", "DAR002 Chart", "DAR002 P2", "DAR002 P2 Chart", _
        "DHS003 Data", "DHS003 Chart", "IRD002 Data", "IRD002 Chart", "IRD003 Data", _
        "IRD003 Chart", "IRD014 Data", "IRD014 Chart", "KWC001 Data", "KWC001 Chart", _
        "NOA002 Data", "NOA002 Chart", "NRL001 Data", "NRL001 Chart", "NVS002 Data", _
        "NVS002 Chart", "NVS004 Data", "NVS004 Chart", "ONR007 Data", "ONR007 Chart", _
        "SBR003 Data", "SBR003 Chart", "SEA001 Data", "SEA001 Chart")).Copy Before:= _
        Workbooks("ASG Project Financial Sheet.xlsx").Sheets(3)

I can post a larger section of code if that helps anyone!

Thanks in advance!

 

[Legacy 373233]

Why dont you cycle through them instead of grabbing them all

Sub move()




For counter = 1 To Workbooks("Libro1").Sheets.Count      'Libro1 is the name of the source workbook, change it
    Sheets(counter).Copy Before:=Workbooks("Libro2").Sheets(1)  'Libro2 is the name of the destination workbook.
    Windows("Libro1").Activate   
Next
End Sub

 

[hg_macro_support]

I'm sorry, I am very new to any sort of coding and do not necessarily understand what your code is trying to show or where I would input it. I was simply using the record a macro option. The error seems to occur during the actual copying of the sheets.

 

[Legacy 373233]

Oh i see.

can you provide me with the source and destination exact workbook names.?


i'll tell you where to input the code once i modify it with your workbook names.

 

[Norie]

Have you checked all the worksheet names?

 

[skywriter]

In doing some testing I found a few things that can cause this same error.

Without seeing the rest of your code I will tell you what I found.

1) If any of the worksheets you are trying to copy don't exist, you will get this error. This means they either aren't there or there is a misspelling of the name of the worksheet, sometimes there are spaces at the end of a name that you might not be aware of. If this code worked for you at one time and you haven't deleted any of the sheets or changed their names then this is probably not the issue.

2) You are copying the sheets before Sheets(3) which means you are trying to copy these sheets before the third sheet in the other workbook. If there are not 3 or more sheets in the other workbook you will get this error.

3) If the other workbook is not open when you run this code you will this error.

4) The last thing I noticed is if the sheet with the sheets to be copied isn't the active sheet you get the error.

 

[hg_macro_support]

In doing some testing I found a few things that can cause this same error.
2) You are copying the sheets before Sheets(3) which means you are trying to copy these sheets before the third sheet in the other workbook. If there are not 3 or more sheets in the other workbook you will get this error.

Thanks!!! I changed the Sheets(3) to Sheets(1) and it worked perfectly.

I appreciate all of your guys input, especially as a newcomer!

cheers

 

[skywriter]

My pleasure, thanks for the feedback.