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!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

=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,109,275
Messages
5,527,726
Members
409,784
Latest member
AdamPriest

This Week's Hot Topics

Top