1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Combining VBA Code

Discussion in 'Business Applications' started by jo15765, Nov 28, 2011.

Thread Status:
Not open for further replies.
  1. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    I have currently been running these two modules separately...is there a way to combine them into one module and run together?
    Code:
    Public Sub Monday_1()
        Dim Varbooks
        Dim varBook
        Dim wb As Excel.Workbook
        Varbooks = Array("Test1", "Test2")
            For Each varBook In Varbooks
                Set wb = Workbooks.Open(Filename:="L:\Monday\" & varBook)
                Run "RefreshOnOpen"
                With wb
                    .SaveAs Filename:="L:\Test_Folder\" & "_" & VBA.Left(ActiveWorkbook.Name, VBA.InStrRev(.Name, ".") - 1) & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
                    .Close False
                End With
            Next varBook
    End Sub
    Public Sub Monday_2()
        Dim Varbooks
        Dim varBook
        Dim varPrograms
        Dim varprogram
        Dim fileName1
        Dim fileName2
        Dim wb As Excel.Workbook
        Dim strPath1 As String
        Dim strpath2 As String
        Dim whichPath As String
        Dim CurrentPath As String
        
        CurrentPath = ActiveWorkbook.Path
        On Error GoTo ErrorCatch
    
        fileName1 = "_RainStorm.xls"
        fileName2 = "_SnowStorm.xls"
        varPrograms = Array("Test1", "Test2")
        Varbooks = Array(fileName1, fileName2)
        
        Dim strPathArr()
        ReDim strPathArr(1 To 2)
        
        For Each varprogram In varPrograms
            strPathArr(1) = "L:\Monday\" & varprogram
            strPathArr(2) = "L:\Monday\" & varprogram & "_New"
            
            For Each varBook In Varbooks
                Set wb = Nothing
                whichPath = InWhichPathArr(strPathArr, varprogram, varBook)
                If Len(Trim(whichPath)) > 0 Then
                    Set wb = Workbooks.Open(Filename:=whichPath & "\" & varprogram & varBook)
                End If
                If Not wb Is Nothing Then
                    Dim wks As Worksheet, qt As QueryTable
                    For Each wks In wb.Worksheets
                        For Each qt In wks.QueryTables
                            qt.Refresh BackgroundQuery:=False
                        Next qt
                    Next wks
                    Set qt = Nothing
                    Set wks = Nothing
                    Application.DisplayAlerts = False
                    wb.SaveAs Filename:="L:\Test_Folder\" & VBA.Left(ActiveWorkbook.Name, VBA.InStrRev(wb.Name, ".") - 1) & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"
                    Application.DisplayAlerts = True
                    wb.Close False
                End If
            Next varBook
        Next varprogram
        GoTo ExitMacro
        
    ErrorCatch:
    MsgBox Err.Description
    
    ExitMacro:
    On Error GoTo 0
    End Sub
    
     
  2. jo15765

    jo15765 Thread Starter

    Joined:
    Oct 11, 2011
    Messages:
    307
    I just took out the End Sub and the Public Sub declaring the 2nd module and added a Next in there. It is functioning almost like I want it, but hey at least I have it combined the way I need it!
     
  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Thanks for posting what you did to solve the problem! If there's something else we can help you with, don't hesitate to ask. :)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028755

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice