Subscript out of Range Error

Rymare

New Member
Joined
Apr 20, 2018
Messages
37
I keep getting a subscript out of range error when I run this code

Code:
Private Sub CommandButton1_Click()
Dim file_path As String
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
file_path = FileSelectBox("*.xlsx")
xlApp.Visible = True
'xlApp.Workbooks.Open FileName:=file_path


'ThisWorkbook.Sheets("completion").Range("z1") = file_path


Application.ScreenUpdating = False
Dim wb As Workbook: Set wb = Workbooks(file_path)
wb.Open


    Dim Lastrow As Long
    
    With wb.Sheets("WOs with Dates")
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
With wb.Sheets("WOs with Dates").Range("a:c" & Lastrow)
    .AutoFilter Field:=1, Criteria1:=TextBox1.Value
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells.Copy
    ThisWorkbook.Sheets("WOs").Activate
    ThisWorkbook.Sheets("WOs").Range("b:c").PasteSpecial
End With
            
ThisWorkbook.Sheets("Completion").Range("Q1").ClearContents
ThisWorkbook.Sheets("completion").Range("z1").ClearContents
Me.Hide
End Sub

At this
Code:
Dim wb As Workbook: Set wb = Workbooks(file_path)

Now I know this file exists, it's literally referring to a file I JUST PICKED with the file picker. Can someone help me understand why I'm getting this error?




Just a little context on what I'm trying to do:

I'm trying to get this code so that when someone opens the file (which is meant to be used as a template then saved for individual use) the userform prompts them for their initials, they then pick a specific file filled with a particular city's work orders and then it copies the values in Column B and C, only if the corresponding Column A has their initials (textbox1.value). Example of scenario:

John Smith and Maria Rojas are both working on Los Angeles, they need the Work orders (column B) and the installation dates (Column C) in their own sheets. Their initials are in Column A (either JS or MR) telling them which work orders are theirs



Any help is very much appreciated!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
Code:
[COLOR=#333333]file_path = FileSelectBox("*.xlsx")[/COLOR]

What is FileSelectBox? Is that another sub?
 

Rymare

New Member
Joined
Apr 20, 2018
Messages
37
I was able to figure it out, and yes it is, it open the select file dialog box, I should have included that in my original code--my bad. Even though I figured it out thank you for the reply! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,725
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top