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

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
85
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Looks like your code got cut off. Not seeing anything below:
With Sheets("ReviewContent")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
What happens with
VBA Code:
With ThisWorkbook.Sheets("ReviewContent")
Assuming that the code is in the same workbook that the "ReviewContent" sheet is in?
 
Upvote 0
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.
 
Upvote 0
Did you test the code line that I posted and what was the result?
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top