Subscript out of range error 9 on windows activate

[Pophil]

I have an excel program xlsm that recently one of the macros I use to export data from another excel workbook says subscript out of range error 9. Basically I have a master excel workbook that has many different tabs. On each tab I have a macro export button that takes the data from the other excel workbook and moves it into the master excel workbook along with other macros I have listed in it. The main workbook is called POOM C REPORT. When I see the debug issue it shows that the Windows("POOM C REPORT").Activate is the issue. Everything I have researched shows that the title of my workbook is different even though the workbook has the same title as POOM C REPORT. The other excel workbooks I open will usually always have a different title so this macro was good because it didn't reference the temp workbook I had open that the data I needed came from. Also in that macro the Windows("POOM C REPORT").Activate is there 3 times so even if the first issue is fixed I have a feeling the other two window activate prompts will show as an issue. Any help you can be is greatly appreciated. I use this report for about 22 different reports. Thank you all


Sub ExportFromMTEL()
'
' ExportFromMTELRIMS3 Macro
'

'
Dim answer As VbMsgBoxResult

answer = MsgBox("Is RIMS Tool & Export from RIMS the ONLY two Excel Workbooks presently open?", vbYesNo, "HOTETZ RIMS TOOL WARNING!")

If answer = vbNo Then

MsgBox "Well then open them...", vbOKOnly, "Duhh!"

End If

If answer = vbYes Then

ActiveWindow.WindowState = xlMinimized
Call COPYMTEL

Selection.Copy
Windows("POOM C REPORT").Activate
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveWindow.WindowState = xlMinimized
Application.DisplayAlerts = False
ActiveWindow.Close SaveChanges:=False
Windows("POOM C REPORT").Activate
ActiveWindow.WindowState = xlMaximized




End If

Windows("POOM C REPORT").Activate
ActiveWindow.WindowState = xlMaximized
Call STEP6
End Sub

 

[Jaafar Tribak]

Yes they are both open at the same time

I meant in the same excel instance.
Check in the Windows Task Manager (Press CTRL +SHIFT + ESC) .Under the Details tab, see how many instances of the excel exe there is.

 

[Pophil]

I meant in the same excel instance.
Check in the Windows Task Manager (Press CTRL +SHIFT + ESC) .Under the Details tab, see how many instances of the excel exe there is.

Oh ok
Under PID it's 6884
Status running
Cpu 00
Memory 265,464K
Uac virtulization. Disabled

 

[Jaafar Tribak]

Oh ok
Under PID it's 6884
Status running
Cpu 00
Memory 265,464K
Uac virtulization. Disabled

How many excel.exe (s) are on the list ?

 

[Pophil]

How many excel.exe (s) are on the list ?

Just 1

 

[Pophil]

Question the excel program will work on other computers but not my main one. What would cause that to show the debug error every time.

 

[MARK858]

What happens if you use

Workbooks("POOM C REPORT").Activate

 

[Pophil]

What happens if you use

Workbooks("POOM C REPORT").Activate

Same thing, debug error shows the same line is the issue.

 

[MARK858]

Put the code below in a module in the POOM C REPORT workbook, then run the code... what number appears in the message box?

Sub Testme()
MsgBox Len(ThisWorkbook.Name)
End Sub

 

[Pophil]

Put the code below in a module in the POOM C REPORT workbook, then run the code... what number appears in the message box?

Sub Testme()
MsgBox Len(ThisWorkbook.Name)
End Sub

18

 

[Pophil]

Put the code below in a module in the POOM C REPORT workbook, then run the code... what number appears in the message box?

Sub Testme()
MsgBox Len(ThisWorkbook.Name)
End Sub

Thank you so much. I just did a combination of things you said to try. I changed to workbook then added .xlsm. Together they both worked but separately they didn't. Can't thank you enough.