Run 2 different Macros saved in different worksheets but same workbook

johnny_raman

New Member
Joined
Mar 26, 2014
Messages
22
Hi,

I am trying to run 2 codes one after the other but wont run.

I am able to run it separately which i have no issues. Below is the code i used to run both.


Code:
Sub Run_Both

Call ADDCLM
Call Temp

End Sub

____________________________________________________________________________________
Code:
Sub ADDCLM()
On Error Resume Next
Dim B737_BBJ_Row As Long
Dim B737_BBJ_Clm As Long
Table1 = ActiveSheet.Range("K2:K2000") ' VLOOKUP VALUE Column
Table2 = ActiveSheet.Range("XFC2:XFC66") ' Range of VLOOKUP ARRAY
B737_BBJ_Row = ActiveSheet.Range("AD2").Row ' Change AD2 with the cell from where you need to start populating the VLOOKUP DATA
B737_BBJ_Clm = ActiveSheet.Range("AD2").Column
For Each cl In Table1
  ActiveSheet.Cells(B737_BBJ_Row, B737_BBJ_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
  B737_BBJ_Row = B737_BBJ_Row + 1
Next cl
On Error Resume Next
Dim Territory_List_Row As Long
Dim Territory_List_Clm As Long
Table1 = ActiveSheet.Range("AA2:AA2000") ' VLOOKUP VALUE Column
Table2 = ActiveSheet.Range("XFD2:XFD66") ' Range of VLOOKUP ARRAY
Territory_List_Row = ActiveSheet.Range("AE2").Row ' Change AE2 with the cell from where you need to start populating the VLOOKUP DATA
Territory_List_Clm = ActiveSheet.Range("AE2").Column
For Each cl In Table1
  ActiveSheet.Cells(Territory_List_Row, Territory_List_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
  Territory_List_Row = Territory_List_Row + 1
Next cl
   With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
   End With
    With ActiveSheet.UsedRange
      .AutoFilter 15, "B737"
      .AutoFilter 30, "<>"
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "B737 BBJ"
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 15, "Non Aircraft Specific"
      .AutoFilter 2, Array("Amsterdam", "Dubai", "Shanghai", "UK Burgess Hill"), xlFilterValues
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Others"
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 31, ""
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 31, "<>"
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
Sheets("DATA_TRANSFER_FIRM").Select
Range("B2").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B1").Select
   ActiveSheet.Select
   Range("A1").Select
      End If
   .Parent.AutoFilterMode = False
   .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 31, "<>"
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dubai", "UK Burgess Hill"), xlFilterValues
      .AutoFilter 14, "Maintenance*"
      If .Cells(Rows.Count, 14).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
Sheets("BAT_HAT_MXT_Firmed").Select
Range("B2").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A1").Select
   ActiveSheet.Select
   Range("A1").Select
      End If
   .Parent.AutoFilterMode = False
.AutoFilter 2, Array("Dubai", "UK Burgess Hill"), xlFilterValues
      .AutoFilter 14, "Maintenance*"
      If .Cells(Rows.Count, 14).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
    End If
   .Parent.AutoFilterMode = False
   End With
   With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .Calculation = xlCalculationAutomatic
   End With
   End Sub

____________________________________________________________________________________


Code:
Sub TEMP()
On Error Resume Next
Dim B737_BBJ_Row As Long
Dim B737_BBJ_Clm As Long
Table1 = ActiveSheet.Range("M2:M2000") ' VLOOKUP VALUE Column
Table2 = ActiveSheet.Range("XFC2:XFC66") ' Range of VLOOKUP ARRAY
B737_BBJ_Row = ActiveSheet.Range("AF2").Row ' Change AF2 with the cell from where you need to start populating the VLOOKUP DATA
B737_BBJ_Clm = ActiveSheet.Range("AF2").Column
For Each cl In Table1
  ActiveSheet.Cells(B737_BBJ_Row, B737_BBJ_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
  B737_BBJ_Row = B737_BBJ_Row + 1
Next cl
On Error Resume Next
Dim Territory_List_Row As Long
Dim Territory_List_Clm As Long
Table1 = ActiveSheet.Range("AC2:AC2000") ' VLOOKUP VALUE Column
Table2 = ActiveSheet.Range("XFD2:XFD66") ' Range of VLOOKUP ARRAY
Territory_List_Row = ActiveSheet.Range("AG2").Row ' Change AG2 with the cell from where you need to start populating the VLOOKUP DATA
Territory_List_Clm = ActiveSheet.Range("AG2").Column
For Each cl In Table1
  ActiveSheet.Cells(Territory_List_Row, Territory_List_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 1, False)
  Territory_List_Row = Territory_List_Row + 1
Next cl
   With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
   End With
    With ActiveSheet.UsedRange
      .AutoFilter 15, "B737"
      .AutoFilter 30, "<>"
      If .Cells(Rows.Count, 15).End(xlUp).Row > 1 Then
         .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "B737 BBJ"
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 17, "Non Aircraft Specific"
      .AutoFilter 2, Array("Amsterdam", "Dubai", "Shanghai", "UK Burgess Hill"), xlFilterValues
      If .Cells(Rows.Count, 17).End(xlUp).Row > 1 Then
         .Columns(17).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Others"
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 33, ""
      If .Cells(Rows.Count, 17).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 33, "<>"
      If .Cells(Rows.Count, 17).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
Sheets("DATA_TRANSFER_TEMP").Select
Range("B2").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("B1").Select
   ActiveSheet.Select
   Range("A1").Select
      End If
   .Parent.AutoFilterMode = False
   .AutoFilter 2, Array("Dallas", "Bombardier - Montreal", "NETC", "BBD Dallas", "Embraer CAE Brazil", "Embraer CAE Dallas"), xlFilterValues
      .AutoFilter 33, "<>"
      If .Cells(Rows.Count, 17).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
      End If
      .Parent.AutoFilterMode = False
      .AutoFilter 2, Array("Dubai", "UK Burgess Hill"), xlFilterValues
      .AutoFilter 16, "Maintenance*"
      If .Cells(Rows.Count, 16).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
Sheets("BAT_HAT_MXT_Temps").Select
Range("B2").Select
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A1").Select
   ActiveSheet.Select
   Range("A1").Select
      End If
   .Parent.AutoFilterMode = False
.AutoFilter 2, Array("Dubai", "UK Burgess Hill"), xlFilterValues
      .AutoFilter 14, "Maintenance*"
      If .Cells(Rows.Count, 16).End(xlUp).Row > 1 Then
         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).EntireRow.Delete
    End If
   .Parent.AutoFilterMode = False
   End With
   With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .Calculation = xlCalculationAutomatic
   End With
   MsgBox "Done"
End Sub
 
Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
As the code is in different worksheets, I suggest you activate the relevant worksheet before each Call and see if that does it.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,341
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top