Run-time error '9': At a loss for where to go

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
Hello,

I am running into an issue any time I try to run a macro with my excel "dashboard" when I have any other workbook open. That includes completely blank workbooks.

Below is the error, and also the last line of code below is where the error seems to happen. I even tried changing the name to the actual Sheet (Sheet4), and still no luck.

subcript.JPG


VBA Code:
Option Explicit

Sub CreateWordDocuments()
Dim CustRow, CustCol, FinalCol As Long
Dim DocLoc, TagName, TagValue, TagRow, TemplName, FileName, TempFileName, ReviewLoc, WebsiteURL As String
Dim WordDoc As Word.Document, WordApp As Word.Application
Dim WordContent As Word.Range

Call Acc_Expand
Call ReqFields
Call TrimURL
Call populatetopics

    TempFileName = Sheets("DSSWorksheet").Range("comp")
    WebsiteURL = Sheets("DSSWorksheet").Range("C40")
    MsgBox "Please allow 1-2 minutes while the review for " & TempFileName & " is processed." & vbCrLf & vbCrLf & "The Dashboard will now minimize, and Word will open when the review is ready", vbExclamation
    ActiveWindow.WindowState = xlMinimized
    
    With Sheets("ReviewContent")

Thanks as always for your help.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Looks like your code got cut off. Not seeing anything below:
With Sheets("ReviewContent")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
That usually means that in the active workbook that you have open that it cannot find any sheet named "ReviewContent".
Make sure that it is named EXACTLY "ReviewContent", with no extra spaces at the beginning or end, and that it is not hidden or protected.
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365

ADVERTISEMENT

It is definitely spelled correctly as it works when no other workbooks are open. This error only happens when I open up other workbooks, even blank ones.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
It is definitely spelled correctly as it works when no other workbooks are open. This error only happens when I open up other workbooks, even blank ones.
You need to make sure that the workbook that contains that worksheet is the activeworkbook when you try to use that.
If another workbook is the activeworkbook at the point in time when you when try to run that line of code, it won't work (because it will be looking in that workbook for a worksheet with that name).
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,993
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What happens with
VBA Code:
With ThisWorkbook.Sheets("ReviewContent")
Assuming that the code is in the same workbook that the "ReviewContent" sheet is in?
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
What happens with
VBA Code:
With ThisWorkbook.Sheets("ReviewContent")
Assuming that the code is in the same workbook that the "ReviewContent" sheet is in?
Correct!

Maybe it is because I am minimizing the excel sheet?

The reason I do that, is it takes quite a while for the code to run and I do not want to user to mess with the excel sheet or just start hitting buttons.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,993
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Did you test the code line that I posted and what was the result?
 

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
76
Office Version
  1. 365
Did you test the code line that I posted and what was the result?

Sorry Mark not quite following.

I should also mention that the code I posted above is in a module, and not within a specific sheet.

I am still relatively new to VB in O365, and am unsure of where the best place is to store my code/macros (module, worksheets etc).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top