locked
Macro Not Working when calling it through another Macro RRS feed

  • Question

  • I have all my Macros written in one Module (Module 1) in a particular project.  I wrote a Macro to copy the active sheet in one workbook and paste it as a sheet to another macro enabled workbook.  So this is the code for that Macro:

    Sub CopySheetToMasterWorkbook()
    '
    ' CopySheetToMasterWorkbook Macro
    ' Declare and Set variables to contain Pathname, Filename and Tabnames
        Dim PathName As String, MasterFileName As String, DataFileName As String, _
            TabName As String
       
        PathName = "Pathname"
        MasterFileName = "Filename of workbook for the Macro to copy the sheet to"
        DataFileName = "The workbook from which the sheet is copied"
        TabName = Workbooks(DataFileName).ActiveSheet.Name
       
        Workbooks.Open FileName:=PathName & MasterFileName
        Workbooks(DataFileName).Sheets(TabName).Copy _
            After:=Workbooks(MasterFileName).Sheets(1)
        Workbooks(MasterFileName).Save
        Workbooks(MasterFileName).Close
    End Sub

    When I Step Into or run this Macro by itself, it works great; but when I call the Macro from another Macro, the Macro just stops after it opens the MasterFile (Workbooks.Open FileName:=PathName & MasterFileName).  It doesn't copy the sheet or Save or Close the workbook.  The code for the Macro which calls the CopySheetToMasterWorkbook Macro is below:

    Sub FormatData()
    ' FormatData Macro

    ' Calls all the SubRoutines to manage data and create Report
        TrimText
        FormatSheet
        RenameSheet
        CopySheetToMasterWorkbook
    End Sub

    Has anyone run into this type of issue?  Is there a property of the Open() method that I need to set when the file is opened?  Please help!

    Thursday, May 30, 2013 2:10 PM

All replies

  • Actually, the Macro doesn't work properly only when I use the shortcut key 'Ctrl+Shift+Q'.  When I run the Macro from the View Macros tool, the macro works just fine.  However, I would like the shortcut key to work.  Any help on this subject would be great.
    Thursday, May 30, 2013 2:53 PM