Run 2 different Macros saved in different worksheets but same workbook

J

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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

Q
  • Question Question
VBA fill right
Replies
2
Views
729
Peter_SSs
Peter_SSs
E
  • Question Question
Filtering values from data source and pasting it into a second workbook
2
Replies
17
Views
1K
kevin9999
K
H
  • Question Question
Unable to autofilter for 'Begins With' and 'Does Not Contain' in same VBA array
Replies
7
Views
2K
honkin
H
tezza
  • Question Question
Option explicit workbook help
Replies
3
Views
610
igold
igold
C
  • Question Question
VBA Filtering
Replies
0
Views
479
chaddres
C

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

Share this page

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