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

 

[Special-K99]

Bottom of this page someone says they have same problem and solution

https://stackoverflow.com/questions/16540071/out-of-range-error-for-windows-activate

 

[Pophil]

I've seen that before. it says you must save macros in modules. When i check the macro it has the module listed. Is there something else to do

 

[Micron]

Make sure it starts with Public and not Private.

 

[Pophil]

Thank you all for trying to help. What makes no sense I've had other people run the macro for me from a different computer and it works. The windows activate error is only happening for me.

 

[MARK858]

Try adding the file extension to the workbook name i.e.

Windows("POOM C REPORT.xlsm").Activate

if it is an xlsm (change extension to suit)

 

[Pophil]

Thank you all for your help. Is there another vba macro to copy another workbook and paste it into my main workbook (POOM C REPORT)
The issue is the temp report I export automatically always has a different title every time. Also depending on the report sometimes I have it pasted in A1 or anywhere in a specific cell that I choose. I have about 25 tabs (sheets) I use this type of Macro on my various reports As you can see the macro I had never referenced the temp file name nor the actual sheet the data was getting pasted into only had it listed as active sheet. The Call commands I can add in after. Thank you all


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

Quote Reply
Report

 

[Jaafar Tribak]

Are the two workbooks opened in the same excel instance?

 

[Pophil]

Are the two workbooks opened in the same excel instance?

Yes they are both open at the same time

 

[Pophil]

Try adding the file extension to the workbook name i.e.

Windows("POOM C REPORT.xlsm").Activate

if it is an xlsm (change extension to suit)

thank unfortunately that didn't fix it