0

I've got 50 wb's Im currently updating using vba. But it's a drag writing (and updating) the same code 50 times, so I'm trying to come up with a method to re-use code. All wb's are equal in structure (sheetnames etc), and the basic "update-vba" should therefore be reusable in all "UpdateGroup-subs". My idea is to place definitions and basic "update-vba" in subs outside the "UpdateGroup-subs", and call upon them when running each "UpdateGroup-sub". But I'm getting a compile error (variable not defined). Is what I'm trying to do even possible? Anyone that can help me out on how to get this to work? I'm uploading 2 versions, one that is working (repeating all code in each "UpdateGroup-sub"), and one that's my attempt at simplify this...

WORKS:

Option Explicit
'************************************************************************

Sub UpdateAllGroups_1_WorksOK()

Dim StartTime As Double
Dim MinutesElapsed As String
StartTime = Timer

    Call UpdateGroup1
    Call UpdateGroup2

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "All Updates is done in " & MinutesElapsed, vbInformation, "Message"

End Sub
'************************************************************************

Private Sub UpdateGroup1()

'DEFINITIONS
Dim fPath, ThisGroupWb, ReportR2ob, ReportR1vo, ReportR2vo As String
Dim WbReport, WbGroup As Workbook
Dim sh_Dash, sh_NewR2ob, sh_NewR1vo, sh_NewR2vo, sh_Time As Worksheet

    fPath = ThisWorkbook.Path
        If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
        End If

Set WbGroup = Workbooks.Open(ThisWorkbook.Path & "\Group1_(M).xlsm")    'must be changed in each UpdateGroup-sub
    With WbGroup
        Set sh_Dash = .Worksheets("Dash")
        Set sh_NewR2ob = .Worksheets("NewR2ob")
        Set sh_NewR1vo = .Worksheets("NewR1vo")
        Set sh_NewR2vo = .Worksheets("NewR2vo")
        Set sh_Time = .Worksheets("Time")
    End With

ThisGroupWb = "Group1_(M).xlsm"     'must be changed in each UpdateGroup-sub
ReportR2ob = "R2ob - Group1.xls"    'must be changed in each UpdateGroup-sub
ReportR1vo = "R1vo - Group1.xls"    'must be changed in each UpdateGroup-sub
ReportR2vo = "R2vo - Group1.xls"    'must be changed in each UpdateGroup-sub


'NEW REPORTS
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Set WbReport = Workbooks.Open(fPath & "\R2ob\" & ReportR2ob)
    WbReport.Sheets(1).Cells.Copy sh_NewR2ob.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R1vo\" & ReportR1vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR2vo.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R2vo\" & ReportR2vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR1vo.Range("A1")
    WbReport.Close False


'STORE AND CLOSE GROUP-WB
Application.Goto sh_Dash.Range("A1"), True
WbGroup.Save
WbGroup.Close False

End Sub
'************************************************************************

Private Sub UpdateGroup2()

'DEFINITIONS
Dim fPath, ThisGroupWb, ReportR2ob, ReportR1vo, ReportR2vo As String
Dim WbReport, WbGroup As Workbook
Dim sh_Dash, sh_NewR2ob, sh_NewR1vo, sh_NewR2vo, sh_Time As Worksheet

    fPath = ThisWorkbook.Path
        If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
        End If

Set WbGroup = Workbooks.Open(ThisWorkbook.Path & "\Group2_(M).xlsm")    'must be changed in each UpdateGroup-sub
    With WbGroup
        Set sh_Dash = .Worksheets("Dash")
        Set sh_NewR2ob = .Worksheets("NewR2ob")
        Set sh_NewR1vo = .Worksheets("NewR1vo")
        Set sh_NewR2vo = .Worksheets("NewR2vo")
        Set sh_Time = .Worksheets("Time")
    End With

ThisGroupWb = "Group2_(M).xlsm"     'must be changed in each UpdateGroup-sub
ReportR2ob = "R2ob - Group2.xls"    'must be changed in each UpdateGroup-sub
ReportR1vo = "R1vo - Group2.xls"    'must be changed in each UpdateGroup-sub
ReportR2vo = "R2vo - Group2.xls"    'must be changed in each UpdateGroup-sub


'NEW REPORTS
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Set WbReport = Workbooks.Open(fPath & "\R2ob\" & ReportR2ob)
    WbReport.Sheets(1).Cells.Copy sh_NewR2ob.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R1vo\" & ReportR1vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR2vo.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R2vo\" & ReportR2vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR1vo.Range("A1")
    WbReport.Close False


'STORE AND CLOSE GROUP-WB
Application.Goto sh_Dash.Range("A1"), True
WbGroup.Save
WbGroup.Close False

End Sub
'************************************************************************

NOT WORKING:

Option Explicit
'************************************************************************

Sub UpdateAllGroups_2_DoesntWork()

Dim StartTime As Double
Dim MinutesElapsed As String
StartTime = Timer

    Call UpdateGroup1
    Call UpdateGroup2

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "All Updates is done in " & MinutesElapsed, vbInformation, "Message"

End Sub
'************************************************************************

Private Sub Definitions()

Dim fPath, ThisGroupWb, ReportR2ob, ReportR1vo, ReportR2vo As String
Dim WbReport, WbGroup As Workbook
Dim sh_Dash, sh_NewR2ob, sh_NewR1vo, sh_NewR2vo, sh_Time As Worksheet

    fPath = ThisWorkbook.Path
        If Right(fPath, 1) = "\" Then
        fPath = Left(fPath, Len(fPath) - 1)
        End If

    With WbGroup
        Set sh_Dash = .Worksheets("Dash")
        Set sh_NewR2ob = .Worksheets("NewR2ob")
        Set sh_NewR1vo = .Worksheets("NewR1vo")
        Set sh_NewR2vo = .Worksheets("NewR2vo")
        Set sh_Time = .Worksheets("Time")
    End With

End Sub
'************************************************************************

Private Sub UpdateGroups()

Set WbGroup = Workbooks.Open(ThisWorkbook.Path & "\ThisGroupWb")

'NEW REPORTS
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Set WbReport = Workbooks.Open(fPath & "\R2ob\" & ReportR2ob)
    WbReport.Sheets(1).Cells.Copy sh_NewR2ob.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R1vo\" & ReportR1vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR2vo.Range("A1")
    WbReport.Close False

    Set WbReport = Workbooks.Open(fPath & "\R2vo\" & ReportR2vo)
    WbReport.Sheets(1).Cells.Copy sh_NewR1vo.Range("A1")
    WbReport.Close False


'STORE AND CLOSE GROUP-WB
Application.Goto sh_Dash.Range("A1"), True
WbGroup.Save
WbGroup.Close False

End Sub
'************************************************************************

Private Sub UpdateGroup1()

Call Definitions
    ThisGroupWb = "Group1_(M).xlsm"
    ReportR2ob = "R2ob - Group1.xls"
    ReportR1vo = "R1vo - Group1.xls"
    ReportR2vo = "R2vo - Group1.xls"
Call UpdateGroups

End Sub
'************************************************************************

Private Sub UpdateGroup2()

Call Definitions
    ThisGroupWb = "Group2_(M).xlsm"
    ReportR2ob = "R2ob - Group2.xls"
    ReportR1vo = "R1vo - Group2.xls"
    ReportR2vo = "R2vo - Group2.xls"
Call UpdateGroups

End Sub
Improve this question
3
  • 1
    On which line exactly do you get the error ? Commented Oct 18, 2018 at 7:59
  • Just as a remark: Declarations like that Dim fPath, ThisGroupWb, ReportR2ob, ReportR1vo, ReportR2vo As String only declare the last variable as string, all others are variant, for an explanation look here. Commented Oct 18, 2018 at 8:14
  • ThisGroupWb = "Group1_(M).xlsm" in "UpdateGroup1-sub". And if I put this line on "pause", error appear on the next line. Commented Oct 18, 2018 at 8:15

1 Answer 1

Reset to default
0

You should use arguments in your subroutine as follows:

'Version 1: using single argument GroupNumber

Option Explicit
'************************************************************************

Sub UpdateAllGroups()

Dim StartTime As Double
Dim MinutesElapsed As String
StartTime = Timer

    Call UpdateGroup(1)
    Call UpdateGroup(2)

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "All Updates is done in " & MinutesElapsed, vbInformation, "Message"

End Sub
'************************************************************************

Private Sub UpdateGroup(ByVal GroupNumber As Long)

    'DEFINITIONS
    Dim fPath, ThisGroupWb, ReportR2ob, ReportR1vo, ReportR2vo As String
    Dim WbReport, WbGroup As Workbook
    Dim sh_Dash, sh_NewR2ob, sh_NewR1vo, sh_NewR2vo, sh_Time As Worksheet

        fPath = ThisWorkbook.Path
            If Right(fPath, 1) = "\" Then
            fPath = Left(fPath, Len(fPath) - 1)
            End If

    'Note that path is composed using supplied argument:
    Set WbGroup = Workbooks.Open(ThisWorkbook.Path & "\Group" & GroupNumber & "_(M).xlsm")
        With WbGroup
            Set sh_Dash = .Worksheets("Dash")
            Set sh_NewR2ob = .Worksheets("NewR2ob")
            Set sh_NewR1vo = .Worksheets("NewR1vo")
            Set sh_NewR2vo = .Worksheets("NewR2vo")
            Set sh_Time = .Worksheets("Time")
        End With

    'Same here:
    ThisGroupWb = "Group" & GroupNumber & "_(M).xlsm"     'must be changed in each UpdateGroup-sub
    ReportR2ob = "R2ob - Group" & GroupNumber & ".xls"    'must be changed in each UpdateGroup-sub
    ReportR1vo = "R1vo - Group" & GroupNumber & ".xls"    'must be changed in each UpdateGroup-sub
    ReportR2vo = "R2vo - Group" & GroupNumber & ".xls"    'must be changed in each UpdateGroup-sub


    'NEW REPORTS
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

        Set WbReport = Workbooks.Open(fPath & "\R2ob\" & ReportR2ob)
        WbReport.Sheets(1).Cells.Copy sh_NewR2ob.Range("A1")
        WbReport.Close False

        Set WbReport = Workbooks.Open(fPath & "\R1vo\" & ReportR1vo)
        WbReport.Sheets(1).Cells.Copy sh_NewR2vo.Range("A1")
        WbReport.Close False

        Set WbReport = Workbooks.Open(fPath & "\R2vo\" & ReportR2vo)
        WbReport.Sheets(1).Cells.Copy sh_NewR1vo.Range("A1")
        WbReport.Close False


    'STORE AND CLOSE GROUP-WB
    Application.Goto sh_Dash.Range("A1"), True
    WbGroup.Save
    WbGroup.Close False

End Sub

Or, closer to what you've done:

'Version 2: using ThisGroupWb, ReportR2ob, ReportR1vo and ReportR2vo

Option Explicit
'************************************************************************

Sub UpdateAllGroups()

Dim StartTime As Double
Dim MinutesElapsed As String
StartTime = Timer

    Call UpdateGroup("Group1_(M).xlsm", "R2ob - Group1.xls", "R1vo - Group1.xls", "R2vo - Group1.xls")
    Call UpdateGroup("Group2_(M).xlsm", "R2ob - Group2.xls", "R1vo - Group2.xls", "R2vo - Group2.xls")

MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "All Updates is done in " & MinutesElapsed, vbInformation, "Message"

End Sub
'************************************************************************

Private Sub UpdateGroup(ByVal ThisGroupWb As String, ByVal ReportR2ob As String, ByVal ReportR1vo As String, ByVal ReportR2vo As String)

    'DEFINITIONS
    Dim fPath As String
    Dim WbReport, WbGroup As Workbook
    Dim sh_Dash, sh_NewR2ob, sh_NewR1vo, sh_NewR2vo, sh_Time As Worksheet

        fPath = ThisWorkbook.Path
            If Right(fPath, 1) = "\" Then
            fPath = Left(fPath, Len(fPath) - 1)
            End If

    Set WbGroup = Workbooks.Open(ThisWorkbook.Path & "\" & ThisGroupWb)    'must be changed in each UpdateGroup-sub
        With WbGroup
            Set sh_Dash = .Worksheets("Dash")
            Set sh_NewR2ob = .Worksheets("NewR2ob")
            Set sh_NewR1vo = .Worksheets("NewR1vo")
            Set sh_NewR2vo = .Worksheets("NewR2vo")
            Set sh_Time = .Worksheets("Time")
        End With

    'Already assigned (call arguments)
    'ThisGroupWb = ...
    'ReportR2ob = ...
    'ReportR1vo = ...
    'ReportR2vo = ...


    'NEW REPORTS
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

        Set WbReport = Workbooks.Open(fPath & "\R2ob\" & ReportR2ob)
        WbReport.Sheets(1).Cells.Copy sh_NewR2ob.Range("A1")
        WbReport.Close False

        Set WbReport = Workbooks.Open(fPath & "\R1vo\" & ReportR1vo)
        WbReport.Sheets(1).Cells.Copy sh_NewR2vo.Range("A1")
        WbReport.Close False

        Set WbReport = Workbooks.Open(fPath & "\R2vo\" & ReportR2vo)
        WbReport.Sheets(1).Cells.Copy sh_NewR1vo.Range("A1")
        WbReport.Close False


    'STORE AND CLOSE GROUP-WB
    Application.Goto sh_Dash.Range("A1"), True
    WbGroup.Save
    WbGroup.Close False

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the tip, but this doesn't change my underlying problem. My wish is in some way to be able to store definitions and "UpdateGroups-vba" in such a way that I can Call upon it and re-use it in each "UpdateGroup-sub".
Take a deeper look: there is only one subroutine: UpdateGroup. When you call it, you must supply an argument, GroupNumber. Then, when subroutine is called, relevant parts are modified to supplied group.
I'll be d.... Your code works just fine! But my head is having some trouble on grasping just why it does so... :-) Are there limitations to your method? Will it still work if group-wb's is named "s3g1", "s3g2", and so on? Or does the code just run through all wb's it finds in this folder?
By the way: Thanks a lot @LS_ᴅᴇᴠ for posting the solution to cure my headache! :-))
This code won't "auto-run" - you must run macro or some other mechanism calls UpdateAllGroups. Then, as in your question, UpdateAllGroups will call UpdateGroup(1) and UpdateGroup(2), simply that, no more automation. About other group names, you must identify all needed inputs to your subroutine, and then make them as arguments and adapt code to those.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.