Runtime Error 9 "Subscript Out of Range" Assistance

hg_macro_support

New Member
Joined
Jun 16, 2016
Messages
7
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:
Code:
   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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
L

Legacy 373233

Guest
Why dont you cycle through them instead of grabbing them all


Code:
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

New Member
Joined
Jun 16, 2016
Messages
7
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.
 
L

Legacy 373233

Guest
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,813
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you checked all the worksheet names?
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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.
 
Last edited:

hg_macro_support

New Member
Joined
Jun 16, 2016
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,682
Messages
5,524,257
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top