Macro button works for 2010 users, but not 2013

HI:

Macro button works for 2010 users, but not 2013. In 2013 we are receiving error "Run Time Error 1004" for macros, the debug goes to this line "ActiveWorkbook.Close SaveChanges:=False"

If we run the macros from the developer tab or VBA it works fine.

I have already deleted the .exd file extensions and restarted.


Attached is the code.

Thanks

Hi there,

The attached version of your routine appears to run correctly on my Office 2013 installation.

It uses the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub BlankVIT()

    Const sBLANK_VIT_NAME   As String = "\\ttcfile11\merchmfa\0_F Drive Re-Org\" & _
                                        "13_Negotiations\01_Models\Merch OFM\" & _
                                        "Merch OFM VIT.xlsb"

    Const sCONTROL          As String = "Control"
    Const sLISTS            As String = "DropDownLists"
    Const sINFO             As String = "Item Info"

    Dim sRelativePath       As String
    Dim rSourceRange        As Range
    Dim rTargetCell         As Range
    Dim sMyFileName         As String
    Dim wbkBlankVIT         As Workbook
    Dim sEventName          As String
    Dim wksControl          As Worksheet
    Dim lErrorNo            As Long

    Application.ScreenUpdating = False

        Set wksControl = ThisWorkbook.Worksheets(sCONTROL)

        wksControl.Activate    '   Not needed if the routine is called from this worksheet

        Set wbkBlankVIT = Workbooks.Open(sBLANK_VIT_NAME)

        Set rSourceRange = wksControl.Range("F7:H57")
        Set rTargetCell = wbkBlankVIT.Sheets(sLISTS).Range("D4")
        Call CopyData(rSourceRange:=rSourceRange, rTargetCell:=rTargetCell)

        Set rSourceRange = wksControl.Range("K7:M57")
        Set rTargetCell = wbkBlankVIT.Sheets(sLISTS).Range("G4")
        Call CopyData(rSourceRange:=rSourceRange, rTargetCell:=rTargetCell)

        Set rSourceRange = wksControl.Range("Z10:Z12")
        Set rTargetCell = wbkBlankVIT.Sheets(sINFO).Range("J7")
        Call CopyData(rSourceRange:=rSourceRange, rTargetCell:=rTargetCell)

        sEventName = wksControl.Range("Event_Name").Value
        sMyFileName = sEventName & " - Blank VIT.xlsb"
        sRelativePath = ThisWorkbook.path & "\" & sMyFileName

        On Error Resume Next
            wbkBlankVIT.SaveAs Filename:=sRelativePath, FileFormat:=xlExcel12
            lErrorNo = Err.Number
        On Error GoTo 0

        If lErrorNo <> 0 Then
            MsgBox "The new workbook has NOT been saved", vbExclamation
        End If

        wbkBlankVIT.Close SaveChanges:=False

        wksControl.Range("Y14") = sRelativePath

    Application.ScreenUpdating = True

End Sub


'=========================================================================================
'=========================================================================================


Private Sub CopyData(rSourceRange As Range, rTargetCell As Range)

    Dim vaDataValues    As Variant
    Dim iNoOfColumns    As Integer
    Dim rTargetRange    As Range
    Dim iNoOfRows       As Integer

    vaDataValues = rSourceRange.Value

    iNoOfColumns = UBound(vaDataValues, 2)
    iNoOfRows = UBound(vaDataValues, 1)

    With rTargetCell
        Set rTargetRange = Range(rTargetCell.Cells(1, 1), _
                                 rTargetCell.Cells(iNoOfRows, iNoOfColumns))
    End With

    rTargetRange.Value = vaDataValues

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg:

Thanks you so much for taking time to try and help me. However; the command "wbkBlankVIT.Close SaveChanges:=False" is still a error. When I run the code from VBA or the Developer tab it works fine, but not using the button. I even created a new button without success.

The code you provided is much better than mine and have learned something new and appreciate that.
Regards
John

Hi again John,

Many thanks for your feedback and for your kind words - glad to have helped, even if only a little.

If you think it would be useful, I can try running your workbooks on my 2013 installation to see if the same thing happens here - just a suggestion.

Best regards,

Greg M