Forum Discussion

clh_1496's avatar
clh_1496
Copper Contributor
May 30, 2022
Solved

combine multiple sub macros into one

Hi,

 

I'm trying to make this one VBA macro that runs as one Sub so that I don't have to run 10+ individual macros, but struggling with working out the indentation, where to put end if etc

 

Option Explicit

Sub AddColumns()

'Inserts 2 Columns at V,W

Worksheets(“Clockings”).Range("V:W").EntireColumn.Insert

End Sub

Sub AddColumns()

'Inserts 2 Columns at Y,Z

Worksheets(“Clockings”).Range("Y:Z").EntireColumn.Insert

End Sub

Sub Sample()

    Dim ws As Worksheet

    Dim LastRow As Long, i As Long

    Dim tmpArray() As String

 

    '~~> This is the relevant sheet

    Set ws = ThisWorkbook.Sheets("Clockings")

 

    With ws

        LastRow = .Range("U" & .Rows.Count).End(xlUp).Row

 

        For i = 2 To LastRow

            If InStr(1, .Range("U" & i).Value, " ") Then

                tmpArray = Split(.Range("U" & i).Value, " ")

                .Range("V" & i).Value = tmpArray(0)

                .Range("W" & i).Value = tmpArray(1)

                'Changes number format in Columns W and V to date and time

                .Range("V:V").NumberFormat = "Date"

                .Range("W:W").NumberFormat = "Time"

 

            End If

        Next i

    End With

End Sub

Sub Sample()

    Dim ws As Worksheet

    Dim LastRow As Long, i As Long

    Dim tmpArray() As String

 

    '~~> This is the relevant sheet

    Set ws = ThisWorkbook.Sheets("Clockings")

 

    With ws

        LastRow = .Range("X" & .Rows.Count).End(xlUp).Row

 

        For i = 2 To LastRow

            If InStr(1, .Range("X" & i).Value, " ") Then

                tmpArray = Split(.Range("X" & i).Value, " ")

                .Range("Y" & i).Value = tmpArray(0)

                .Range("Z" & i).Value = tmpArray(1)

                'Changes number format in Columns Y and Z to date and time

                .Range("Y:Y").NumberFormat = "Date"

                .Range("Z:Z").NumberFormat = "Time"

 

            End If

        Next i

    End With

End Sub

Sub Delete_a_Column()

'delete column U

Worksheets("Clockings").Range("U:U").EntireColumn.Delete

Worksheets("Clockings").Range("X:X").EntireColumn.Delete

End Sub

 

 

Sub AddColumns()

'Inserts Six Columns at C,D,E,F,G,H

Worksheets(“Clockings”).Range("C:H").EntireColumn.Insert

End Sub

Sub AddHeader()

    Worksheets("Clockings")..Range("C1").Formula = "Activity ID"

    Worksheets("Clockings").Range("D1").Formula = "Activity Description"

    Worksheets("Clockings").Range("E1").Formula = "Shift"

    Worksheets("Clockings").Range("F1").Formula = "Type"

    Worksheets("Clockings").Range("G1").Formula = "MT"

    Worksheets("Clockings").Range("H1").Formula = "Build"

    Worksheets("Clockings").Range("AI1").Formula = "Role"

    Worksheets("Clockings").Range("AJ1").Formula = "Squad"

    Worksheets(“Clockings”).Range("V1").Formula = "Start Date"

    Worksheets(“Clockings”).Range("W1").Formula = "Start Time"

    Worksheets(“Clockings”).Range("Y1").Formula = "End Date"

    Worksheets(“Clockings”).Range("Z1").Formula = "End Time"

End Sub

Sub AddFormula()

'Inserts specific formulae to cells C2,D2.E2.F2.G2 and H2

    Dim Formulas(1 To 2) As Variant

    With ThisWorkbook.Worksheets("Clockings")

        Formulas(1) = "=IFERROR(VLOOKUP(B2,'1811 SO'!A:C,2,0),VLOOKUP(B2,'1813 SO'!A:C,2,0))"

        Formulas(2) = "=IFERROR(VLOOKUP(C2,'1811 SO'!B:D,2,0),VLOOKUP(C2,'1813 SO'!B:D,2,0))"

        Formulas(3) = "=IF(ISNUMBER(SEARCH(""Sling"",D2)),""Sling/Lab"",IF(ISNUMBER(SEARCH(""Dress"",D2)),""NDT Dressing Support"",IF(ISNUMBER(SEARCH(""Downtime"",D2)),""Downtime"",IF(ISNUMBER(SEARCH(""jigs"",D2)),""Jigs"",IF(ISNUMBER(SEARCH(""NC"",C2)),""NCR"",IF(ISNUMBER(SEARCH(""M2"",C2)),""Change"",IF(ISNUMBER(SEARCH(""M3"",C2)),""Change"",""Earning"")))))))"

        Formulas(4) = "=MID(C2,4,2)"

        Formulas(5) = "=IF(ISNA(VLOOKUP(C2,'1811 SO'!B:B,1,FALSE)), ""Build III"", ""Build II"")"

        .Range("C2:H2").Formula = Formulas

'Changes number format in Columns B and C to general

        .Range("C:H").NumberFormat = "General"

    End With

End Sub

Sub AddFormula()

'Inserts specific formulae to cells AI2 and AJ2

    Dim Formulas(1 To 2) As Variant

    With ThisWorkbook.Worksheets("Clockings")

        Formulas(1) = "=VLOOKUP(AH2,'Employee Info'!A:C,3,0)"

        Formulas(2) = "=VLOOKUP(AH2,'Employee Info'!B:D,3,0)"

        .Range("AI2:AJ2").Formula = Formulas

'Changes number format in Columns AI and AJ to general

        .Range("AI:AJ").NumberFormat = "General"

    End With

End Sub

 

 

  • clh_1496 

    As a single macro, it could look like this:

    Sub DoAll()
        Dim ws As Worksheet
        Dim LastRow As Long, i As Long
        Dim tmpArray() As String
        Dim Formulas() As Variant
        '~~> This is the relevant sheet
        Set ws = ThisWorkbook.Sheets("Clockings")
        With ws
            'Inserts Six Columns at C,D,E,F,G,H
           .Range("C:H").EntireColumn.Insert
            'Inserts 2 Columns at V,W
            .Range("V:W").EntireColumn.Insert
            LastRow = .Range("U" & .Rows.Count).End(xlUp).Row
            For i = 2 To LastRow
                If InStr(1, .Range("U" & i).Value, " ") Then
                    tmpArray = Split(.Range("U" & i).Value, " ")
                    .Range("V" & i).Value = tmpArray(0)
                    .Range("W" & i).Value = tmpArray(1)
                    'Changes number format in Columns W and V to date and time
                    .Range("V:V").NumberFormat = "Date"
                    .Range("W:W").NumberFormat = "Time"
                End If
            Next i
            LastRow = .Range("X" & .Rows.Count).End(xlUp).Row
            For i = 2 To LastRow
                If InStr(1, .Range("X" & i).Value, " ") Then
                    tmpArray = Split(.Range("X" & i).Value, " ")
                    .Range("Y" & i).Value = tmpArray(0)
                    .Range("Z" & i).Value = tmpArray(1)
                    'Changes number format in Columns Y and Z to date and time
                    .Range("Y:Y").NumberFormat = "Date"
                    .Range("Z:Z").NumberFormat = "Time"
                End If
            Next i
            'delete column U
            .Range("U:U").EntireColumn.Delete
            .Range("X:X").EntireColumn.Delete
            'Inserts 2 Columns at Y,Z
            .Range("Y:Z").EntireColumn.Insert
            .Range("C1").Formula = "Activity ID"
            .Range("D1").Formula = "Activity Description"
            .Range("E1").Formula = "Shift"
            .Range("F1").Formula = "Type"
            .Range("G1").Formula = "MT"
            .Range("H1").Formula = "Build"
            .Range("AI1").Formula = "Role"
            .Range("AJ1").Formula = "Squad"
            .Range("V1").Formula = "Start Date"
            .Range("W1").Formula = "Start Time"
            .Range("Y1").Formula = "End Date"
            .Range("Z1").Formula = "End Time"
            'Inserts specific formulae to cells C2,D2.E2.F2.G2 and H2
            ReDim Formulas(1 To 5)
            Formulas(1) = "=IFERROR(VLOOKUP(B2,'1811 SO'!A:C,2,0),VLOOKUP(B2,'1813 SO'!A:C,2,0))"
            Formulas(2) = "=IFERROR(VLOOKUP(C2,'1811 SO'!B:D,2,0),VLOOKUP(C2,'1813 SO'!B:D,2,0))"
            Formulas(3) = "=IF(ISNUMBER(SEARCH(""Sling"",D2)),""Sling/Lab"",IF(ISNUMBER(SEARCH(""Dress"",D2)),""NDT Dressing Support"",IF(ISNUMBER(SEARCH(""Downtime"",D2)),""Downtime"",IF(ISNUMBER(SEARCH(""jigs"",D2)),""Jigs"",IF(ISNUMBER(SEARCH(""NC"",C2)),""NCR"",IF(ISNUMBER(SEARCH(""M2"",C2)),""Change"",IF(ISNUMBER(SEARCH(""M3"",C2)),""Change"",""Earning"")))))))"
            Formulas(4) = "=MID(C2,4,2)"
            Formulas(5) = "=IF(ISNA(VLOOKUP(C2,'1811 SO'!B:B,1,FALSE)), ""Build III"", ""Build II"")"
            .Range("C2:H2").Formula = Formulas
            'Changes number format in Columns B and C to general
            .Range("C:H").NumberFormat = "General"
            'Inserts specific formulae to cells AI2 and AJ2
            ReDim Formulas(1 To 2)
            Formulas(1) = "=VLOOKUP(AH2,'Employee Info'!A:C,3,0)"
            Formulas(2) = "=VLOOKUP(AH2,'Employee Info'!B:D,3,0)"
            .Range("AI2:AJ2").Formula = Formulas
            'Changes number format in Columns AI and AJ to general
            .Range("AI:AJ").NumberFormat = "General"
        End With
    End Sub

3 Replies

      • clh_1496 

        As a single macro, it could look like this:

        Sub DoAll()
            Dim ws As Worksheet
            Dim LastRow As Long, i As Long
            Dim tmpArray() As String
            Dim Formulas() As Variant
            '~~> This is the relevant sheet
            Set ws = ThisWorkbook.Sheets("Clockings")
            With ws
                'Inserts Six Columns at C,D,E,F,G,H
               .Range("C:H").EntireColumn.Insert
                'Inserts 2 Columns at V,W
                .Range("V:W").EntireColumn.Insert
                LastRow = .Range("U" & .Rows.Count).End(xlUp).Row
                For i = 2 To LastRow
                    If InStr(1, .Range("U" & i).Value, " ") Then
                        tmpArray = Split(.Range("U" & i).Value, " ")
                        .Range("V" & i).Value = tmpArray(0)
                        .Range("W" & i).Value = tmpArray(1)
                        'Changes number format in Columns W and V to date and time
                        .Range("V:V").NumberFormat = "Date"
                        .Range("W:W").NumberFormat = "Time"
                    End If
                Next i
                LastRow = .Range("X" & .Rows.Count).End(xlUp).Row
                For i = 2 To LastRow
                    If InStr(1, .Range("X" & i).Value, " ") Then
                        tmpArray = Split(.Range("X" & i).Value, " ")
                        .Range("Y" & i).Value = tmpArray(0)
                        .Range("Z" & i).Value = tmpArray(1)
                        'Changes number format in Columns Y and Z to date and time
                        .Range("Y:Y").NumberFormat = "Date"
                        .Range("Z:Z").NumberFormat = "Time"
                    End If
                Next i
                'delete column U
                .Range("U:U").EntireColumn.Delete
                .Range("X:X").EntireColumn.Delete
                'Inserts 2 Columns at Y,Z
                .Range("Y:Z").EntireColumn.Insert
                .Range("C1").Formula = "Activity ID"
                .Range("D1").Formula = "Activity Description"
                .Range("E1").Formula = "Shift"
                .Range("F1").Formula = "Type"
                .Range("G1").Formula = "MT"
                .Range("H1").Formula = "Build"
                .Range("AI1").Formula = "Role"
                .Range("AJ1").Formula = "Squad"
                .Range("V1").Formula = "Start Date"
                .Range("W1").Formula = "Start Time"
                .Range("Y1").Formula = "End Date"
                .Range("Z1").Formula = "End Time"
                'Inserts specific formulae to cells C2,D2.E2.F2.G2 and H2
                ReDim Formulas(1 To 5)
                Formulas(1) = "=IFERROR(VLOOKUP(B2,'1811 SO'!A:C,2,0),VLOOKUP(B2,'1813 SO'!A:C,2,0))"
                Formulas(2) = "=IFERROR(VLOOKUP(C2,'1811 SO'!B:D,2,0),VLOOKUP(C2,'1813 SO'!B:D,2,0))"
                Formulas(3) = "=IF(ISNUMBER(SEARCH(""Sling"",D2)),""Sling/Lab"",IF(ISNUMBER(SEARCH(""Dress"",D2)),""NDT Dressing Support"",IF(ISNUMBER(SEARCH(""Downtime"",D2)),""Downtime"",IF(ISNUMBER(SEARCH(""jigs"",D2)),""Jigs"",IF(ISNUMBER(SEARCH(""NC"",C2)),""NCR"",IF(ISNUMBER(SEARCH(""M2"",C2)),""Change"",IF(ISNUMBER(SEARCH(""M3"",C2)),""Change"",""Earning"")))))))"
                Formulas(4) = "=MID(C2,4,2)"
                Formulas(5) = "=IF(ISNA(VLOOKUP(C2,'1811 SO'!B:B,1,FALSE)), ""Build III"", ""Build II"")"
                .Range("C2:H2").Formula = Formulas
                'Changes number format in Columns B and C to general
                .Range("C:H").NumberFormat = "General"
                'Inserts specific formulae to cells AI2 and AJ2
                ReDim Formulas(1 To 2)
                Formulas(1) = "=VLOOKUP(AH2,'Employee Info'!A:C,3,0)"
                Formulas(2) = "=VLOOKUP(AH2,'Employee Info'!B:D,3,0)"
                .Range("AI2:AJ2").Formula = Formulas
                'Changes number format in Columns AI and AJ to general
                .Range("AI:AJ").NumberFormat = "General"
            End With
        End Sub

Resources