Close Window
Free Ebook
Get Your Free Excel
Top 15 Excel Tutorials from
Instant Access!

Workbooks.close Error

I am having an error while attemtping to use VBA to close a workbook.

the piece of code receiving the error looks like this:


and the error description is something like:

"Rune-time error 9: Subscript is out of range"

varFileName is String variable containing the full name (ex. C:/Files/document.xls) of the spreadsheet which must be closed.

I am unclear on what that error message actually means. Any suggestions?

(I can post the entire macro tomorrow if necessary, but its at work right now)

Thanks for any suggestions on the cause of this error.

Free Excel Courses

Similar Excel Tutorials

How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...

Excel macro that allows you to select multiple workbooks and have all of their worksheets automatically combined in ...

How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...

How to use the VLOOKUP function across multiple workbooks in Excel. This will create a link between the workbooks t ...

Helpful Excel Macros

- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe

- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set

Similar Topics

How can i solve a debugger error that states "run-time error '9'...Subscript out of range.

for a search box code provided by Joe Was

Is there an on error exit sub command. I would like my Macro to just stop running if there is an error instead of an error message popping up. Thanks in advance

Hi all,

I've been getting this error on occasion recently.

- It's a shared document
- There is usually 10-12 people using the file at the same time

I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.

Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?


Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes. The real catch is that the workbook has macros. I used the 'Share Workbook' function under tools. At the end of the setup, it told me that the macros would not work properly. The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click 'end' everytime on the error screen. Any suggestions?


I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.

All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.

If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.

It's like the buttons are being disabled when I close or open the file. Any suggestions?

I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.

When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."

I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.


I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!


I have created a simple macro in this excel spreadsheet. Everything works fine until I sent the attachment to my boss. It does not work and keep showing s pop up error msg.

What could be wrong?

I get the error message "reference is not valid" each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.

I have a sheet with raw data a sheet with pivot tables a sheet with a dashboard and a simple macro
I don't have any #REF cells either.

Someone has any idea of what could be the problem?



I am trying to insert a range of cell in the body of an outlook email with the same format. The code I am using now is below and it does insert anything in the email body.


Sub Mail()
Dim OutApp As Object
 Dim OutMail As Object
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
 With OutMail
 .To = ""
 .CC = ""
 .BCC = ""
 .Subject = "Burden Report"
 .Body = ActiveSheet.Range("A1:D12")
 .Attachments.Add ("C:\Documents and Settings\cbelcher\Desktop\Burden Report.xls")
End With
 On Error GoTo 0
 Set OutMail = Nothing
 Set OutApp = Nothing
 Set objMsg = Nothing
End Sub

I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: /

Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String

Set appIE = CreateObject("InternetExplorer.Application")
URL = " / "

With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With

On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
Exit For
End If


End Sub

We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!


I am using


in a piece of VBA and it causes an error if the sheet does not have a filter applied! Is there an if statement I can use to check if the data is filtered?

Thanks - this site is an absolute life saver!



I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.


Sub Step4()
 On Error Resume Next
End Sub

I'm sure it's something simple... like me !

Any help much appreciated


I often use if statements to return empty cells, for example:


The trailing "" returns an empty cell. The problem is, it is not TRULY empty. If I fill that formula down, I cannot, for example, jump from one "ERROR" cell to the next by hitting Ctrl arrow-down because it seems to think that these empty cells have contents.

Is there a way to designate, in such a formula as above, to return a TRULY empty cell?


Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).


Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
For Each C In Range("A1", Range("A65536").End(xlUp))
 If C 0 Then
 With C.Offset(0, 1)
 Set Picture = Nothing
 On Error Resume Next
 Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
 Picture.Top = .Top
 Picture.Left = .Left
 Picture.LockAspectRatio = msoTrue
 Picture.Width = 157
 Picture.Height = 138

 End With
 End If
Next C
End Sub

I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help


Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?



I have a login form with an Exit button. I want my workbook to close as I click Exit button.

Sale = A1
Cost = B1
Margin = C1

I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?


I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.

Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.

I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).

What modifications do I make to this to get this to work per above requirements?

Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub

I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()
'Dim pdf As String
 On Error Resume Next
 'pdf file to open
 pdf = "K:\PDF\mypdf.pdf"
 'open the pdf file
 ActiveWorkbook.FollowHyperlink pdf
End Sub

So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()
 Dim pdf As AcroPDDoc
 Dim strPDF As String
 Set pdf = CreateObject("AcroExch.PDDoc")
 'pdf file to open
 strPDF = "K:\PDF\mypdf.pdf"
 'open the pdf file
 pdf.Open strPDF
End Sub

Any ideas what could be wrong with either approach?


I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?


Edit: I have read this: / /> Doesn't seem to cover the issue I describe

Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window > 1 (filename), the code that I get in VBA is Windows("filename").Activate. This doesn't translate to when I have two different workbooks with different filenames open.

Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.

- Michael

I need to disable all kinds of messages that could appear in front of the user when it opens the workbook. I am already using DisplayAlerts=False and On Error Resume Next? Can we also do this through excel application itself and not using VBA ?

Related pages

excel autofill columnexcel chart add vertical lineexcel formula based on color of cellpercent markdown calculatorautocomplete feature in excelrandom tournament generatorexcel pivot table distinct counthow to change number format to millions in excelhow to find merged cells in excelnumeric alphabetexcel split panesexcel vba clear contentserror 424 object required vb6sumif blankexcel sendkeysshared workbookclustered bar chart excelmatch vba exceljob cost sheet templatefile not found vba6.dllpallet load calculatorrounding up in excel formulavba reserved wordsreorder quantity calculationsheet name formula excelflowcharts in visiohow to convert digits into words in excel 2007softball stats spreadsheetshortcut keys for subscript and superscript in powerpointrefresh pivot table datavba translatorconvert csv to pipe delimitedcalculating interest excelpowerpivot multiple data sourcesapplication.vlookupexcel percent formulaexcel formula for finding duplicatesintercept function exceldisable macroexcel merge shortcutcreate an amortization table in excelbex analyzertimesheet calculator with breaksexcel pound signswordart excelapplication wait vbasumif less thanhow to delete duplicate rows in excelmorningstar login username passwordmandays calculationexcel formula for time cardconvert kilo stonescagr formula for excelsharepoint list synchronizervba generate random numberexcel vba minimumpercent rank excelhow to calculate a percentage increase in excelhow to copy spreadsheet with formulaspayroll hours calculator converterconvert standard time to military time in excelalternate shading excelsales tracker spreadsheetdistmonreverse concatenateunprotecting excel workbookvba excel switchvba insert a rowlock formula in excelvba randwatermark on excel spreadsheetchange excel delimiterballoon payment calculator excelvba excel left functiongolf handicap calculator excelcalculate gini coefficient excelcounting empty cells in excelexcel stops respondingvics bill of lading template