Run time 1004 error on Mac


Board Regular
May 23, 2013
Hi Excel Gurus,

I'm back with VBA challenges working between Mac and Windows machines. Earlier I encountered simple formatting errors such as an angled apostrophe and one that goes vertical that made my code unresponsive. Now I have some new challenges that are baffling me. All code is created on a Windows machine.

The code asks the user to select a target workbook. All sheets from target are copied to master macro workbook for processing. The target workbook name is captured along with a date.

On Macs running Excel v 15.29 I get a run time error 1004 after the target workbook is selected in the Open Dialog. Here's the error and why I'm concerned there is a format problem. The cancel portion works and the message box appears as it should saying 'Operation Cancelled'.

Are there different formats of quotation marks that might cause this error?

But on a Mac with Excel v 15.24 the code operates as it should and the target workbook's sheets are successfully copied to the master workbook and the user can enter the date.

Run-time error 1004

" could not be found. Check the spelling of the file names, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.

Here's the code.

Sub OpenTarget()
  ' I created an If/Then statement in order to determine the variable subQuit value used elsewhere.
    Dim FileToConsolodate As Boolean
    FileToConsolodate = Application.Dialogs(xlDialogOpen).Show
    If FileToConsolodate Then
         'MsgBox "File Opened"
         subQuit = 0
         MsgBox "Operation Cancelled"
         subQuit = 1
         Exit Sub
        End If
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Now opened workbook name is captured here as public string. Short book name is also captured- losing last 14 characters
    GetTargetName = ActiveWorkbook.Name

    'shortening the name by removing the dates for version control- this will allow of auto-populate the Save As dialog.
    ShortTargetName = Left(GetTargetName, Len(GetTargetName) - 14)

    newdate = InputBox("Enter Today's Date")
    file_name = ShortTargetName & "_" & newdate

    'copy each sheet to MasterScript
    Dim wb1 As Workbook
    Set wb1 = Workbooks(GetTargetName)
    Dim wb2 As Workbook
    Set wb2 = Workbooks("MasterScript.xlsm")

        Dim i As Long
            For i = 1 To wb1.Sheets.Count
            wb1.Sheets(i).Copy After:=wb2.Sheets(wb2.Sheets.Count)

    ' create an Index page. Count sheet starting at sheet 2 and placing Index at sheet 2 to keep 1 the home page with buttons
        Application.DisplayAlerts = False
            If ActiveSheet.Name = "Index" Then
            End If
        Application.DisplayAlerts = True

    Dim myCount As Long, writeRow As Long

        writeRow = 2
        ' new sheet is created call Index
        Worksheets.Add Before:=Sheets(2)
        Sheets(2).Name = "Index"
        'adding column header in A1.
        Selection.Value = "Tab Index"
            For myCount = 3 To Sheets.Count
                Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & writeRow), Address:="", SubAddress:="'" & Sheets(myCount).Name & "'!A1", TextToDisplay:=Sheets(myCount).Name
        writeRow = writeRow + 1
    Next myCount
    Worksheets.Add Before:=Sheets(2)
    Sheets(2).Name = "RecordingScript"
    ActiveCell.Value = "File Path"
    ActiveCell.Value = "File Name"
    ActiveCell.Value = "Recording Prompts"
    ActiveCell.Value = "Notes"
    Call anotherModule
    Call anotherModule2
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Any thoughts on this are greatly appreciated.

Thanks in advance,

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple


New Member
Dec 1, 2017
Hey Matt_O,

Did you ever get a response to this, or figure it out some other way? I am having the same problem, and am hoping that you know how to solve it by now (a year after you originally posted...)

Upvote 0


Board Regular
May 23, 2013
Hi rlzack,

Wow. I completely forgot about this problem. I never received a response nor could I find a solution on-line.

In the end I had to use a hack. The Mac user needed to open the workbook hosting the macro and the 'target' workbook containing the data to be manipulated. In order for this to work I had to write code to remind the Mac user to close all other workbooks besides the two required documents before continuing processing the data. This was a sloppy work around but it did the trick.

I'm still baffled why my code worked on older versions of Excel running on a Mac but not v15.29

Hopefully there is some type of work around for you.

Upvote 0

Forum statistics

Latest member

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
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 "".
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