vba - run time error '9': Subscript out of range

Question

I am trying to copy 8 different ranges from 8 different tables in a worksheet. So I came up with this coding below. I used union method to combine these ranges, but it returned a

run-time error '9': Subscript out of range

at the following line Set range1 = Sheets("Sheet1").Range("O24").

Can anyone tell me where I did wrong? I can't seem to detect where is my mistake.

Sub ONSHORE()

'Last cell in column
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long
Dim range1 As Range, range2 As Range, range3 As Range, range4 As Range, range5 As Range, range6 As Range, range7 As Range, range8 As Range, multipleRange As Range

Set range1 = Sheets("Sheet1").Range("O24")
Set range2 = Sheets("Sheet1").Range("AA40, AC40")
Set range3 = Sheets("Sheet1").Range("AA56, AC56")
Set range4 = Sheets("Sheet1").Range("AA72, AC72")
Set range5 = Sheets("Sheet1").Range("AA88, AC88")
Set range6 = Sheets("Sheet1").Range("AA104, AC104")
Set range7 = Sheets("Sheet1").Range("AA120, AC120")
Set range8 = Sheets("Sheet1").Range("AA130, AC130")
Set multipleRange = Union(range1, range2, range3, range4, range5, range6, range7, range8)

Set WS = Worksheets("Sheet1")

Dim wb As Workbook, wb2 As Workbook
Dim vFile As Variant
Dim i As Integer

'Set source workbook
Set wb = ActiveWorkbook

'Open the target workbook
vFile = Application.GetOpenFilename("Excel-files,*.xlsx", MultiSelect:=True)

If IsArray(vFile) Then
    For i = LBound(vFile) To UBound(vFile)
        Set wb2 = Workbooks.Open(vFile(i))

        'if the user didn't select a file, exit sub
        If TypeName(vFile) = "Boolean" Then Exit Sub

        With WS
            Set LastCell = .Cells(.Rows.Count, "D").End(xlUp)
            LastCellRowNumber = LastCell.Row + 1
        End With

        'Set selectedworkbook
        Set wb2 = ActiveWorkbook

        'Select cells to copy
        wb2.Worksheets("Sheet1").Range(multipleRange).Copy

        'Go back to original workbook you want to paste into
        wb.Activate

        'Paste starting at the last empty row
        wb.Worksheets("Sheet1").Range("D" & LastCellRowNumber).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        'Close and save the workbook you copied from
        wb2.Save
        wb2.Close
    Next i

    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End If

End Sub

are you sure you have a sheet named "Sheet1" ? if you take the line of Set WS = Worksheets("Sheet1") and put it above these lines, does it return an error ? — Shai Rado
– Shai Rado, Commented Nov 3, 2016 at 6:29
I changed my coding as you said. It still returned an error. — Deve1
– Deve1, Commented Nov 3, 2016 at 6:35
which means that you don't have a sheet named "Sheet1" , right ? — Shai Rado
– Shai Rado, Commented Nov 3, 2016 at 6:37
I have Sheet1. But it's working after I named Sheet1 to something else. Thank you — Deve1
– Deve1, Commented Nov 3, 2016 at 6:52

user6432984 · Accepted Answer · 2016-11-03 06:32:31Z

2

I'm guessing that Sheets("Sheet1") does not exist. It could have a space in it Sheets("Sheet 1").

There is no reason to union the ranges. The code below is analogous to your union.

Set multipleRange = Sheets("Sheet1").Range("O24, AA40, AA56, AA72, AA88, AA104, AC120, AA130 ")

answered Nov 3, 2016 at 6:32

user6432984

Sign up to request clarification or add additional context in comments.

2 Comments

Deve1 Over a year ago

I tried your code but it still returns the same error to your code.

user6432984 Over a year ago

You are getting the error is because there is no worksheet named Sheet1.

Mukul Varshney · Accepted Answer · 2016-11-03 06:35:20Z

0

I got the error 9 when sheet1 was missing while error 1004 when sheet1 is there with no data in range. So the answer for your problem is sheet1 missing.

answered Nov 3, 2016 at 6:35

Mukul Varshney

3,1511 gold badge14 silver badges19 bronze badges

Collectives™ on Stack Overflow

vba - run time error '9': Subscript out of range

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related