How to run macro inside a really busy workbook

[sailorliones]

Here's what's currently going on.

Workbook A: Contains data, and Macro A which calls Macro B in Workbook B
Workbook B: Contains no data, but contains Macro B which takes data the file which calls it, and spits it out in a text file
Business requested the data and macro as in separate locations.

Here's the problem: When I run Macro A (which then calls and runs Macro B), the run time is VERY SLOW, like 30 minutes slow. This is because business created Workbook A with lots of sheets, each sheet containing lots of links, formatting, and conditional formatting. I've tried turning off calculations, screen updating, alerts, and events, but no dice on faster speed. I know it's not Macro A or B's fault, because a slightly modified version of both was used before on a different file, and it's <5 seconds fast.

Try #1: Manually copying Sheet A (which contains the data for the macros) into new Workbook C, running Macro C (copy of Macro A) and B off Workbook C.
Speed < 5 secs if Workbook A is closed
Speed = 25 mins if Workbook A is open (even though Workbook A is not called)

Try #2: Adding logic to Macro A to create new Workbook C, copy Sheet A into Workbook C, and have Workbook C call Macro B
Speed = 30 mins because Workbook A is open

Try #3: Doing Try #2, but close Workbook A once Macro B is running. This closes Workbook A, and thus should speed up everything.
All macros stop when Macro A is closed since Macro A, which is the first macro run, resides in Workbook A (which is now closed).


Any ideas?

 

[mole999]

are you copying formulas across, or is it the intention to do so

what does the code look like ?

 

[sailorliones]

i am not copying formulas. all i'm doing is passing 2 variables to Macro B: the directory where the output should be sent, and the name of the workbook which has the data


MACRO A

Sub Run_Forecast_Macro()


'/*=============================================================================================*\
'/*                                                                                             *\
'/*                                    DEFINING VARIABLES                                       *\
'/*                                                                                             *\
'/*=============================================================================================*\


    Dim macrodirectory As String                    'Directory of the macro file on the Sharepoint site
    Dim forecastname As String                      'Name of the forecast workbook
    Dim forecastdirectory As String                 'Directory of the forecast workbook


    macrodirectory = "C:\Active Work\Forecast Macro\3C"
    forecastname = ActiveWorkbook.Name
    forecastdirectory = ThisWorkbook.Path
    
'/*=============================================================================================*\
'/*                                                                                             *\
'/*                            RUNNING THE MACRO FROM SHAREPOINT                                *\
'/*                                                                                             *\
'/*=============================================================================================*\
    
    Application.ScreenUpdating = False
    
    'Opening the macro file from Sharepoint
    Workbooks.Open (macrodirectory & "/3C_Macro.xlsm"), Password:="easy", WriteResPassword:="easy"
            
    
    'Running the macro from the macro file and passing it the name and the file path of the forecast workbook so the
    '   macro can run on the forecast workbook (instead of the macro workbook), and the output file can be saved at
    '   the same directory as the forecast workbook (instead of the macro workbook)
    
        
    Application.Run "3C_Macro.xlsm!Forecast_Macro", forecastname, forecastdirectory
     
   
    Application.DisplayAlerts = True
    
End Sub

MACRO B

Public Sub Forecast_Macro(forecastname As String, forecastdirectory As String, originalforecast As String)
'
'Summary: Macro will take the forecast information from the new cash forecast layout for Auto or Credit and output
'            it to a txt for later manual import into CMM




'/*=============================================================================================*\
'/*                                                                                             *\
'/*                                  DEFINING VARIABLES                                         *\
'/*                                                                                             *\
'/*=============================================================================================*\


'GIVING INSTRUCTIONS
   Dim answer As Integer            'So the analyst can decide if he/she still wants to run the macro after reading the
                                    '   macro instructions


'HIDING THE MACRO'S ACTIVITY
   Dim forecast_tab As String       'The forecast tab name which contains the CMM forecast layout
   Dim MessageBox As String         'To contain the message box message


'CREATE A NEW WORKSHEET FOR THE CMM MACRO
   Dim WS As Worksheet              'The worksheet (CMM Input) which will contain the macro output before it is exported to the text file


'BEGINNING OF THE MASTER LOOP (3)
' (1) Output Variables:
   Dim amount As Double             'Variable used to only copy forecasting amounts from the forecast tab, otherwise, formats are
                                    '        copied as well
   Dim payment_type As String       'Payment type: R = receipt, P = disbursement, dependent on whether the forecasted amount is in the
                                    '   Receipts category (R) or in the Disbursements category (P)
   Dim entity_id As String          'Entity ID: business unit
   Dim currency_code As String      'Currency code: currency of forecast
   Dim input_date As Date           'Input Date: date the forecast file is submitted
   Dim bank_id As String            'Bank ID: unique identifier for each forecasting entry
   Dim bank_account As String       'Bank Account Number
    
' (2) Static Variables:
   Dim firstrow As Integer          'Number of first row (hardcoded to 10 and modified depending on whether all or 1 bank account is run)
   Dim lastrow As Integer           'Number of last row (from bottom of worksheet to the top based on column G if all entities are run
                                    '   or the last row of the individual forecast if only one entity is run)
   Dim lastcol As Integer           'Number of last column (from right of worksheet to the left based on row 10)
   Dim firstcol As Integer          'Number of first column (hardcoded to 7, as the macro loops on col G: as long as col G has
                                    '       a value, the macro will pull a number for the forecast, regardless if the daily
                                    '       forecast has a value or is blank
   Dim foundentity As Boolean       'Boolean as to whether or not the single entity is found
                                    'TRUE = found       FALSE = not found
   
' (3) Counters:
   Dim row_tracker As Integer       'Tracker variable, keeps track of the row the loop is on
   Dim col_tracker As Integer       'Tracker variable, keeps track (relatively) of which column the loop is on


'EXPORTING MACRO OUTPUT TO TXT
   Dim newdirectorypath As String   'Stores the file path of the new csv workbook which contains the macro output (if the
                                    '       responsibility is given to a new analyst, the macro will still function as intended)
   Dim myRow As Range               'For looping through rows when printing the macro output to the text file
   Dim myColumn As Range            'For looping through columns when printing the macro output to the text file
   Dim nFileNum As Long             'For printing the macro output to the text file
   


'ITEMS WHICH ARE HARDCODED
'    - firstrow variable
'    - firstcol variable
'    - output variables (except payment_type & amount)
'    - value of value date is fixed on row 10
  
  
'/*=============================================================================================*\
'/*                                                                                             *\
'/*                                  GIVING INSTRUCTIONS                                        *\
'/*                                                                                             *\
'/*=============================================================================================*\


'Workbooks(originalforecast).Close savechanges:=False


MessageBox = "1.  If you would like to add or delete rows and columns, you can do so.  The macro was created to "
MessageBox = MessageBox & "automatically adjust for changes to the format.  "
MessageBox = MessageBox & "However, the first cashflow type must be on row 10 and the first forecast date must be "
MessageBox = MessageBox & "in column H." & vbCrLf
MessageBox = MessageBox & "2.  If you wish to reforecast one or more days, please leave the remaining forecast "
MessageBox = MessageBox & "the same.  The macro treats any blank amounts as $0." & vbCrLf
MessageBox = MessageBox & "3.  The macro output file will be located in the same folder as the forecasting file." & vbCrLf
MessageBox = MessageBox & "4.  BE CAREFUL!  If you run the macro but the previous output file was not renamed, "
MessageBox = MessageBox & "the previous file will be overwritten by the new one.  There is no way to undo this." & vbCrLf
MessageBox = MessageBox & vbCrLf
MessageBox = MessageBox & vbCrLf
MessageBox = MessageBox & "Do you still want to run the macro?" & vbCrLf
MessageBox = MessageBox & "YES = runs for all entities" & vbCrLf
MessageBox = MessageBox & "NO = runs for only 1 entity" & vbCrLf
MessageBox = MessageBox & "CANCEL = macro does not run" & vbCrLf


answer = MsgBox(MessageBox, vbYesNoCancel + vbQuestion, "MACRO INSTRUCTIONS")


If answer = vbCancel Then
    Workbooks("3C_Macro.xlsm").Close savechanges:=False
    End
End If






'/*=============================================================================================*\
'/*                                                                                             *\
'/*                              HIDING THE MACRO'S ACTIVITY                                    *\
'/*                                                                                             *\
'/*=============================================================================================*\
  
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual




Workbooks(forecastname).Activate
               
forecast_tab = ActiveSheet.Name




'/*=============================================================================================*\
'/*                                                                                             *\
'/*                          CREATE A NEW WORKSHEET FOR THE CMM MACRO                           *\
'/*                                                                                             *\
'/*=============================================================================================*\


Set WS = Sheets.Add
ActiveSheet.Name = "CMM Input"


Range("A1").Select




'/*=============================================================================================*\
'/*                                                                                             *\
'/*                      DEFINING VARIABLES INDEPENDENT OF USER CHOICE                          *\
'/*                                                                                             *\
'/*=============================================================================================*\


firstcol = 7                'This value is the first column - 1, so the col_tracker loop will be correct
payment_type = "R"          'Defaulting to receipts because the first forecast data will always be a receipt
firstrow = 10               'First row of the first forecast
foundentity = False         'If the user chooses to search for a single entity, the default is that the entity is not found
                            'Used in determining the name of the output file:
                            '   If the user runs for all entities, foundentity = False as it never changes
                            '   If the user runs for one entity, foundentity = True if the entity is found
                            '   If user selects Cancel or searches for one entity not in the file, the macro will exit with no output file


'Defining lastrow and lastcol
With ActiveSheet
    lastrow = Sheets(forecast_tab).Cells(.Rows.Count, "g").End(xlUp).Row
    lastcol = Sheets(forecast_tab).Cells(8, .Columns.Count).End(xlToLeft).Column
End With


'/*=============================================================================================*\
'/*                                                                                             *\
'/*                                 ALL ENTITIES MASTER LOOP                                    *\
'/*                                                                                             *\
'/*=============================================================================================*\


   
If answer = vbYes Then


    'Defining variables dependent on user clicking Yes
    entity_id = Sheets(forecast_tab).Range("C1").Value
    currency_code = Sheets(forecast_tab).Range("C2").Value
    input_date = Sheets(forecast_tab).Range("D3").Value         'Static: changed C5 to D5, modified for month/day - day/month issue
    bank_id = Sheets(forecast_tab).Range("C4").Value
    bank_account = Sheets(forecast_tab).Range("C5").Value


    
    'Master loop for all entities
    For row_tracker = firstrow To lastrow
        For col_tracker = 1 To lastcol - firstcol
        
            'Reseting the Entity ID, Currency, Input Date, Bank ID and Bank Account Number for the new/next forecast
            If Sheets(forecast_tab).Cells(row_tracker, 1).Value = "ENTITY ID" Then
                entity_id = Sheets(forecast_tab).Cells(row_tracker, 3).Value
                currency_code = Sheets(forecast_tab).Cells(row_tracker + 1, 3).Value
                input_date = Sheets(forecast_tab).Cells(row_tracker + 2, 4).Value
                bank_id = Sheets(forecast_tab).Cells(row_tracker + 3, 3).Value
                bank_account = Sheets(forecast_tab).Cells(row_tracker + 4, 3).Value
                payment_type = "R"
            End If
            
            If Sheets(forecast_tab).Cells(row_tracker, 1).Value = "DISBURSEMENTS" Then
                payment_type = "P"
            End If
            
                        
            'Writing the data to CMM Input tab
            If Not Sheets(forecast_tab).Cells(row_tracker, firstcol).Value = "" Then
                If Not Sheets(forecast_tab).Cells(row_tracker, firstcol).Value = "Long Name" Then
              
                    'Amount is the absolute value of the forecasted amount because in the forecast, all disbursements will be (-) and all receipts
                    '   will be (+), as indicated by the payment type of P/R.  When there is a refund issued to us, it will show up as a negative
                    '   disbursement as there is no refund cashflow type in the receipts area.  However, refunds will not be part of the forecast,
                    '   and will only be part of the variance analysis.
                    amount = Abs(Sheets(forecast_tab).Cells(row_tracker, firstcol).Offset(0, col_tracker).Value)
    
                    'Displaying the values as required: Entity ID + Payment Type + Currency Code + Value Date + Amount + "Day"
                    '       + Cashflow Type + Unique Identifier + Bank ID + Bank Account Number
                    'Unique Identifier = Last 18 digits of Bank Account + Cash Flow Type + Payment Type + Value Date
                       
                    'Sheets(forecast_tab).Cells(6, firstcol): changed 10 to 6, modified for month/day - day/month issue
                    
                    ActiveCell.Value = entity_id & "," & _
                        payment_type & "," & _
                        currency_code & "," & _
                        Sheets(forecast_tab).Cells(4, firstcol).Offset(0, col_tracker) & "," & _
                        amount & "," & _
                        "day" & "," & _
                        Sheets(forecast_tab).Cells(row_tracker, firstcol).Offset(0, -2).Value & "," & _
                        Right(bank_account, 18) & _
                            Sheets(forecast_tab).Cells(row_tracker, 5) & _
                            payment_type & _
                            Format(Sheets(forecast_tab).Cells(4, firstcol).Offset(0, col_tracker), "yyyymmdd") & "," & _
                        bank_id & "," & _
                        bank_account
                        
                    ActiveCell.Offset(1, 0).Select
                End If
            End If
        Next col_tracker
    Next row_tracker
    
End If




'/*=============================================================================================*\
'/*                                                                                             *\
'/*                              INDIVIDUAL ENTITY MASTER LOOP                                  *\
'/*                                                                                             *\
'/*=============================================================================================*\


If answer = vbNo Then


    entity_id = InputBox("Please enter the entity for which you want to run this macro (CASE SENSITIVE):")
    
    'If the user presses Cancel instead of typing an entity_id
    If entity_id = "" Then
        WS.Delete                           'Deleting the CMM Input worksheet
        Workbooks("3C_Macro.xlsm").Close savechanges:=False    'Closing the macro workbook
        End                                 'Exiting the macro without creating an output file because entity was not found
    End If
    
    'Finding the first row of data of the intended entity's forecast
    For row_tracker = 1 To lastrow
        If Sheets(forecast_tab).Cells(row_tracker, 3).Value = entity_id Then
            foundentity = True                      'Setting foundentity = True because the entity is found
            firstrow = row_tracker + 9              'Resetting the firstrow variable to the first row of the found entity's forecast instead of the
                                                    '   first row of the first forecast
        End If
                
        If foundentity = True Then Exit For         'Exiting the loop
    Next row_tracker
    
    
    'Master loop for single entity
    If foundentity = True Then
        'Finding the last row of data of the intended entity's forecast
        '       It doesn't matter if this row is not the exact last row, as long as all rows between this one and the actual last row are blanks.  The loop
        '       excludes blank rows.  However, this row must be blank as well
        For row_tracker = firstrow To lastrow
            If Sheets(forecast_tab).Cells(row_tracker, 1).Value = "ENTITY ID" Then
                lastrow = row_tracker - 1               'Resetting the lastrow variable to the last row of the found entity's forecast so the masterloop will
                                                        '   only loop within the intended entity's forecast, instead of to the bottom of the worksheet
            End If
            If lastrow = row_tracker - 1 Then Exit For  'Exiting the loop
        Next row_tracker
        
        'Core of the master loop
        For row_tracker = firstrow To lastrow
            For col_tracker = 1 To lastcol - firstcol
         
                'Defining variables dependent on user clicking No
                entity_id = Sheets(forecast_tab).Cells(firstrow - 9, 3).Value
                currency_code = Sheets(forecast_tab).Cells(firstrow - 8, 3).Value
                input_date = Sheets(forecast_tab).Cells(firstrow - 7, 4).Value
                bank_id = Sheets(forecast_tab).Cells(firstrow - 6, 3).Value
                bank_account = Sheets(forecast_tab).Cells(firstrow - 5, 3).Value
                
                'Changing the payment type if moving into the Disbursements section
                If Sheets(forecast_tab).Cells(row_tracker, 1).Value = "DISBURSEMENTS" Then
                    payment_type = "P"
                End If
                
                            
                'Writing the data to CMM Input tab
                If Not Sheets(forecast_tab).Cells(row_tracker, firstcol).Value = "" Then
                    If Not Sheets(forecast_tab).Cells(row_tracker, firstcol).Value = "Long Name" Then
                
                        'Amount is the absolute value of the forecasted amount because in the forecast, all disbursements will be (-) and all receipts
                        '   will be (+), as indicated by the payment type of P/R.  When there is a refund issued to us, it will show up as a negative
                        '   disbursement as there is no refund cashflow type in the receipts area.  However, refunds will not be part of the forecast,
                        '   and will only be part of the variance analysis.
                        amount = Abs(Sheets(forecast_tab).Cells(row_tracker, firstcol).Offset(0, col_tracker).Value)
        
                        'Displaying the values as required: Entity ID + Payment Type + Currency Code + Value Date + Amount + "Day"
                        '       + Cashflow Type + Unique Identifier + Bank ID + Bank Account Number
                        'Unique Identifier = Last 18 digits of Bank Account + Cash Flow Type + Payment Type + Value Date
                           
                        'Sheets(forecast_tab).Cells(6, firstcol): changed 10 to 6, modified for month/day - day/month issue
                        
                        ActiveCell.Value = entity_id & "," & _
                            payment_type & "," & _
                            currency_code & "," & _
                            Sheets(forecast_tab).Cells(4, firstcol).Offset(0, col_tracker) & "," & _
                            amount & "," & _
                            "day" & "," & _
                            Sheets(forecast_tab).Cells(row_tracker, firstcol).Offset(0, -2).Value & "," & _
                            Right(bank_account, 18) & _
                                Sheets(forecast_tab).Cells(row_tracker, 5) & _
                                payment_type & _
                                Format(Sheets(forecast_tab).Cells(4, firstcol).Offset(0, col_tracker), "yyyymmdd") & "," & _
                            bank_id & "," & _
                            bank_account
                            
                        ActiveCell.Offset(1, 0).Select
                    End If
                End If
            Next col_tracker
        Next row_tracker
    Else
        MsgBox ("Entity was not found.  No output file will be processed.")
        WS.Delete                           'Deleting the CMM Input worksheet
        Workbooks("3C_Macro.xlsm").Close    'Closing the macro workbook
        End                                 'Exiting the macro without creating an output file because entity was not found
    End If
    


End If


'/*=============================================================================================*\
'/*                                                                                             *\
'/*                               PRINTING MACRO OUTPUT TO TXT                                  *\
'/*                                                                                             *\
'/*=============================================================================================*\


'Format: C-US-9100 USD 10242014 Forecast Input.txt for single entity
'Format: 10242014 Forecast Input.txt for all entities
If foundentity = True Then          'Single entity was run
    newdirectorypath = forecastdirectory & "\" & entity_id & " " & bank_id & " " & bank_account & " " & currency_code & " " & Format(input_date, "yyyymmdd") & _
        " " & "Forecast Input.txt"
ElseIf foundentity = False Then     'All entities were run
    newdirectorypath = forecastdirectory & "\" & Format(input_date, "yyyymmdd") & " " & "Forecast Input.txt"
End If


nFileNum = FreeFile
    
'So there are no windows popping up if the output file overwrites a previous output file, or when the WS tab is deleted
Application.DisplayAlerts = False
    
'Opening the text file for the printing of the output, and printing the output to the text file
Open newdirectorypath For Output As #nFileNum
    For Each myRow In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        For Each myColumn In Range(myRow.Cells, Cells(myRow.Row, Columns.Count).End(xlToLeft))
            Print #nFileNum, myColumn.Text
        Next myColumn
    Next myRow
Close #nFileNum


WS.Delete




Application.DisplayAlerts = True
MsgBox "Macro is finished."


'Making sure nothing changes on the Macro workbook from Sharepoint
Workbooks("3C_Macro.xlsm").Close savechanges:=False
   


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic


Workbooks(forecastname).Close savechanges:=False


End Sub