Forum Discussion
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
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
In which order should the macros be run? In the order as listed?
- clh_1496Copper Contributor
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