Hi,

I ran into a few situations where I could see that if I need to specify the active workbook and sheet. I thought it should be a simple thing but I ran into an issue. I can only make it work if I dont specify the folder path which doesnt make much sense to me. If I use the full path and file name in one string I get a "Runtime error '9' Subscript out of range" error message at the code line where I "Set wb ... ".

For me it would be better if specified the active wb using the full path and I dont really get why it should be a problem doing it. Can someone tell me what am I missing...?

If I cannot specify the folder, all workbooks which I need to be activated will need to be in the same folder ... it doesnt make sense to me ... (???)


Option Explicit
Sub Test()
Dim wb As Workbook
Dim ws As Worksheet
 
Dim FilePath1 As String, FilePath2 As String, ActWBStr As String
' FilePath1 = "C:\Users\niels\OneDrive - Niels Molgaard Pedersen\Production\Development\Order & Sales templateTest.xlsm"
'                  "C:\Users\niels\OneDrive - Niels Molgaard Pedersen\Production\Development\Order & Sales templateTest.xlsm"  '  (Copied file path from ms explorer is the same...(?))
FilePath2 = "Order & Sales templateTest.xlsm"

ActWBStr = ActiveWorkbook.Name
MsgBox "Active wb is " & ActWBStr
'MsgBox "New active workbook will be " & FilePath1
MsgBox "New active workbook will be " & FilePath2

'Set wb = Workbooks("C:\Users\niels\OneDrive - Niels Molgaard Pedersen\Production\Development\Order & Sales templateTest.xlsm")
'Set wb = Workbooks(FilePath1)
Set wb = Workbooks(FilePath2)
wb.Activate

Set ws = Sheets("PartNumbers")
ws.Select

End Sub