Subscript out of range error after importing data from another workbook to userform

121 views Asked by At

i have a userform which can open another workbook to import some data. This second workbook is closed after that with

OpenBook.Close False

Then i save this data to the first workbook with a button in the userform. Everything works perfect.

However if i open this second workbook in excel before the data is saved, then by clicking the save button i get an "Subscript out of range" error. (when setting ranges in the first workbook, ie.

Set list1 = Worksheets("Special name").Range("Special column")

It seems that the focus is on the second workbook, and there is no such worksheet.

How can i bring back this focus to the first workbook?

I've tried

ThisWorkbook.Activate

with no luck.

Thanks!

edit: I use this code to open the second workbook:

FileToOpen = Application.GetOpenFilename(filefilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    Set tsh = OpenBook.Sheets(1)
0

There are 0 answers