Subscript out of range (Error 9) in vba

Question

I am getting an error Subscript out of range (Error 9) in vba. I cant find the problem and i searched it everywhere. My code is asking user for a workbook name then asking for select a worksheet, after user select the sheet. It will copy and paste between two sheets. Can someone please help me and here is my code and if you run it then you will see the error thanks

       Function WorksheetExists(WSName As String) As Boolean
         On Error Resume Next
          WorksheetExists = Worksheets(WSName).Name = WSName
         On Error GoTo 0
       End Function

       Sub Button1_Click()
          Dim shname As String
          Dim wb As String
             wb = Application.GetOpenFilename
               If wb <> "False" Then Workbooks.Open wb

          Do Until WorksheetExists(shname)
            shname = InputBox("Enter sheet name")
              If Not WorksheetExists(shname) Then
                MsgBox shname & " doesn't exist!", vbExclamation
               Else
                  WSName = shname

                    Sheets(shname).Select


      ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = Workbooks(Dir(wb)).Worksheets(shname).Cells(1, 1)


        End If
          Loop


       End Sub

where did workbook 'book11' come from? is it open in the same instance of excel? — RowanC, Jul 16 '14 at 02:01
book11 this is active workbook and im trying to open up another workbook and select a worksheet then copy and paste between two sheets — user3793997, Jul 16 '14 at 02:10
possible duplicate of [Subscript out of range run time error 9](http://stackoverflow.com/questions/24665691/subscript-out-of-range-run-time-error-9) — David Zemens, Jul 16 '14 at 02:18

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

Two potential issues here:

When you do: Workbooks(wb), wb holds the entire file path to your workbook. You only need the name of the workbook, not the whole path. In the simplest case you can replace wb with Dir(wb), but it's more generic to use other approaches. See this SO question about how to extract the file name from the path.
I'm assuming "Book11" is valid but you need to make sure this works in all cases too. Also, make sure "Sheet1" is valid for "Book11" too. This is some hard coded indexing going on there, I can only assume you're ok with that.

edited May 23 '17 at 10:33

Community

1
1

answered Jul 16 '14 at 01:59

djikay

10,450
8
41
52

I tried by doing wb.shname.cells(1,1) = sheets("sheet2").cells(1,1) still same error, if you run my code then you will see , its showing an error when i try to copy and paste between two sheets – user3793997 Jul 16 '14 at 02:04
`wb` is just a string, not a `Workbook` object, so `wb.shname` doesn't make sense. – djikay Jul 16 '14 at 02:05
1

It doesn't work in this way. Just try the following line: `ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = Workbooks(Dir(wb)).Worksheets(shname).Cells(1, 1)`. This is going to use the active workbook's "Sheet1" (it must exist). – djikay Jul 16 '14 at 02:10
i just tried the following code and get an error same one subscript out of range Workbooks(Dir(wb)).Worksheets(shname).Cells(1, 1) – user3793997 Jul 16 '14 at 02:13
@user3793997: You did: `Dir("wb")`, that's wrong. Remove the double quotes, it should be: `Dir(wb)`. – djikay Jul 16 '14 at 02:21
thanks it did worked thanks a lot and quick question what does Dir mean – user3793997 Jul 16 '14 at 02:25
Yes, it's a built-in VBA function ([MSDN](http://msdn.microsoft.com/en-us/library/dk008ty4(v=vs.90).aspx)): *Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.* – djikay Jul 16 '14 at 02:28

Subscript out of range (Error 9) in vba

1 Answers1