Sub prompts to Open File but get Run Time Error 9 - Subscript out of Range

[jordsbaker]

Hi Team Amazing,

I need some help.

I have a sub that allows users to select a workbook and then open it. I then prompt them to select the tab that contains the data required.

I then want to copy all of the data from this tab and paste it into the workbook where the Sub is located.

However, everytime I get to the part of activating the chosen tab (selected through an Input box) I get a Subscript out of Range Error.

I assumed that this was because of the variables so have made them public but still get the error.

When I run I get all the way through the part where I want to select the sheet (with name saved as Tabname).

I have tried to run from Sub Importmix() with the file path saved directly into Path2 opposed to selecting it in Sub Test() but still get the error.

Please help.
Public tabname As String
Public ActiveSheet1 As Worksheet
Public path2 As String
Sub test()
Dim intResult As Integer
'Dim path As String

Dim fD As FileDialog
Set fD = Application.FileDialog(msoFileDialogFilePicker)
With fD
.Title = "Select a Path"
.AllowMultiSelect = False
.Show
End With
'importmix fD.SelectedItems(1)
path2 = fD.SelectedItems(1)
Call Importmix
End Sub

Sub Importmix()
Dim DataToCopy As Range
Dim FirstColumn As Range
Dim FirstColumnLetter As String
Dim LastColumn As Range
Dim LastColumnLetter As String
Dim LastRow As Long
Dim Firstrow As Long
Dim tabname As String

Workbooks.Open (path2)


tabname = InputBox("What is the name of the Tab holding the learning records?")
Workbooks(path2).Activate
Worksheets(tabname).Select



End Sub

 

[hateme28]

Have you tried workbooks(path2).worksheets(tabname).select instead of Workbooks(path2).Activate
Worksheets(tabname).Select

 

[jordsbaker]

Hi Hateme28,

Thanks for the lightning quick reply.

I made the change you suggested but still not working.

It appears that the issue is that when I open another workbook within a Sub, it won't allow me to then activate that workbook as it is mid sub? I think this as the process works to activate a sheet based on the input box if it is in the same workbook that the sub runs so must be something about activating a new workbook.

Regards

Jord

 

[hateme28]

I’m thinking the error could be in selecting the sheet. Im not near my computer, but I wonder if inserting quotes into the sheet name would work. Like, worksheets(“”” & tabname & “””). I’d also try hard coding a sheet name to test it. Like workbooks(path2).worksheets(”Sheet1”).select

 

[jordsbaker]

Hi Again,

I have found an example online that prompts the user to select a workbook and then copies all data from with in it. I note that this one avoids trying to activate a particular sheet to copy data from and loops through all sheets and simply adds all data into a single one.

Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim Sheet As Worksheet
Dim PasteStart As Range
Set wb1 = ActiveWorkbook
Set PasteStart = [sheet1!A1]
'Worksheets("Sheet1").Select - can't get this part to work.
Cells.Select 'Selects all data in current sheet
Selection.ClearContents
FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a Report to Parse", _
FileFilter:="Report Files *.xlsx (*.xlsx),")
If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen) 'opens selected workbook

For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)
End With
Next Sheet


End If
wb2.Close
End Sub

 

[jordsbaker]

Thanks for the further thoughts.

I have tried both suggestions and still get the same error. Just doesn't like activating a worksheet mid sub...but perhaps it is something else.

 

[jordsbaker]

Looking at the othet example i xan see the 'set' wb2 as the opened workbook then applied actions to this.

Perhaps of i do similar and creata a variable wb as worksheet and then Set wb2 = Workbooks.Open(Filename:=FileToOpen) 'opens selected workbook. Then

wb2.Sheets(path2).cells.select

And so on...

Ill try that when i get home.

 

[hateme28]

I think that’s what we were missing. I believe that should work for you.