Results 1 to 6 of 6
  1. #1
    natonstan is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    37

    Run a second module after 1st

    I'm a complete newbie here, I have two macros, I run one macro then in the finished sheet I run the second, I'm trying to combine them into one macro so that when the first process finishes it goes into the second. I have both sets of code in separate Modules in the same workbook.



    The 1st macro runs fine, then at the end I want to trigger the second macro titled DKRateVarianceMacro, can someone give me the exact code to simply run this second macro?

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    at the last line in macro1, put RUNMACRO macro2

  3. #3
    natonstan is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    37
    I was trying that, as well as Call but the second macro just does not run.

    Here's exactly how the first macro ends, am I missing something?:

    RunMacro DkRateVarianceMacro

    End Sub

  4. #4
    natonstan is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    37
    Here's the code for the second Macro, I even tried simply pasting at the end of the first set of code, but no luck..

    Code:
    Sub DkRateVarianceMacro()
    Dim myBook As Workbook
    Dim CADsheet, Summarysheet, DKData As Worksheet
    Dim myRow, myCol, DKVar, myRange As Long
    Dim mySupRange As Long
    ' Setting Active Workbook
    Set myBook = ActiveWorkbook
    Set CADsheet = myBook.Sheets("CAD")
    Set Summarysheet = myBook.Sheets("CurrenciesAndDeviantSuppliers")
    ' Pulls the Supplier names from the DK Rate Sheet
    Sheets.Add.Name = "DKRateVariances"
    Set DKData = ActiveSheet
    myRow = 4
    myCol = 4
    Do Until CADsheet.Cells(2, myCol) = ""
        DKData.Cells(myRow, 1) = CADsheet.Cells(2, myCol)
        myCol = myCol + 1
        myRow = myRow + 1
        
    ' Pulls the Currency labels from DK Rate Sheet
    Loop
    myRow = 5
    myCol = 2
    Do Until myBook.Sheets("Summary").Cells(myRow, 1) = ""
        DKData.Cells(2, myCol) = myBook.Sheets("Summary").Cells(myRow, 1)
        DKData.Cells(3, myCol) = myBook.Sheets("Summary").Cells(myRow, 7)
        DKData.Cells(1, myCol) = myBook.Sheets("Summary").Cells(myRow, 2)
        myRow = myRow + 1
        myCol = myCol + 1
    Loop
    myRow = 4
    myCol = 2
    Do Until DKData.Cells(2, myCol) = ""
        Summarysheet.Activate
        myRange = Cells.Find(What:=DKData.Cells(1, myCol), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).row
        DKVar = 4
        Do Until Summarysheet.Cells(myRange, DKVar) = ""
            DKData.Activate
            mySupRange = Cells.Find(What:=Summarysheet.Cells(myRange, DKVar), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).row
            If (Summarysheet.Cells(myRange + 4, DKVar) - Summarysheet.Cells(myRange + 4, 2)) / Summarysheet.Cells(myRange + 4, 2) <> 0 Then
                DKData.Cells(mySupRange, myCol) = (Summarysheet.Cells(myRange + 4, DKVar) - Summarysheet.Cells(myRange + 4, 2)) / Summarysheet.Cells(myRange + 4, 2)
                DKData.Cells(mySupRange, myCol).NumberFormat = "0.00%"
            End If
            DKVar = DKVar + 1
            
        
        
        Loop
        DKData.Cells(1, myCol) = ""
        
        myRow = myRow + 1
        myCol = myCol + 1
    Loop
    ' Formatting Cells
    DKData.Cells(1, 1) = "DK Rate Variance Percentage By Supplier"
    DKData.Cells(3, 1) = "Digikey Sales Rate"
    DKData.Cells(1, 1).Interior.ColorIndex = 15
    DKData.Range("B3:P3").Interior.ColorIndex = 15
    DKData.Cells(1, 1).Font.Bold = True
    DKData.Range("A3:A86").Font.Bold = True
    DKData.Range("B2:P2").Font.Bold = True
    DKData.Range("B3:P3").Font.Bold = True
    DKData.Range("B2:P2").Interior.ColorIndex = 15
    DKData.Range("A1:P1").Merge
    DKData.Range("A3:P86").Borders.LineStyle = xlContinuous
    DKData.Cells(1, 1).HorizontalAlignment = xlCenter
    DKData.Range("A2:A86").Interior.ColorIndex = 15
    DKData.Range("A:P").Columns.AutoFit
     
    
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This appears to be in Excel rather than Access? What Excel calls macros, Access calls VBA code (there are separate things known as macros, and RunMacro would be used there). I don't work directly in Excel much, but try this at the end of the first function:

    DkRateVarianceMacro()

    It may not want the parentheses.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    OOOH, no you cant run subs in macros,
    macros can only run FUNCTIONS.

    runCode DkRateVarianceMacro()

    so convert your sub to functon.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. What is Module?
    By glen in forum Modules
    Replies: 1
    Last Post: 09-14-2012, 09:11 AM
  2. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  3. Module Help
    By gumbi17 in forum Modules
    Replies: 3
    Last Post: 10-20-2011, 03:38 PM
  4. Run a Module
    By mchadwick in forum Modules
    Replies: 7
    Last Post: 09-02-2011, 09:24 AM
  5. Replies: 4
    Last Post: 05-16-2011, 04:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums