Continue Macro run until end of Values in Excel

[ShanShami]

Hello All,
I need your support for below.
I have around 12 Macros running based on a cell values which currently I am changing manually.
Now I want the cell value to be changed automatically based on a excel table. So instead of manually adding cell value macro should pick that value from excel table and sun the macro.
For each value there will be around 12 macros will run.
Please assist with this.

Regards,

 

[Monty]

Hello Shan

Please find below code as pwr yiur requirement.

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer
    
    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to your actual sheet name
    
    ' Assuming your table starts from cell A1 and has one column
    For i = 1 To ws.ListObjects("TableName").ListRows.Count
        ' Read cell value from the table
        cellValue = ws.ListObjects("TableName").ListColumns(1).DataBodyRange(i, 1).Value
        
        ' Run the corresponding macro based on the cell value
        Select Case cellValue
            Case "Value1"
                Call Macro1
            Case "Value2"
                Call Macro2
            ' Add more cases for other values and corresponding macros
            ' Case "Value3": Call Macro3
            ' Case "Value4": Call Macro4
            ' ...
        End Select
    Next i
End Sub

Sub Macro1()
    ' Your code for Macro1 goes here
End Sub

Sub Macro2()
    ' Your code for Macro2 goes here
End Sub

' Add more subs for each macro if required

 

[ShanShami]

Thank you Monty, but I am stuck while applying this code.
I have two columns in table and values I need to consider is of column A.
Also, I need to consider filtered values based on Column B (which we will do manually)
And there are 'n' number of values, the macro "Pricing" should run for each filtered Value from table.
The "Pricing" macro is calling 12 macros.

>>> use code - tags <<<

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer
   
    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change "Sheet1" to your actual sheet name
   
    ' Assuming your table starts from cell A1 and has one column
    For i = 1 To ws.ListObjects("TableName").ListRows.Count
        ' Read cell value from the table
        cellValue = ws.ListObjects("TableName").ListColumns(1).DataBodyRange(i, 1).Value
       
        ' Run the corresponding macro based on the cell value
        Select Case cellValue
            Case "Value1"
                Call Pricing
           
        End Select
    Next i
End Sub

 

[ShanShami]

How do I paste code here? This time I pasted as a text.

 

[Monty]

Thank you Monty, but I am stuck while applying this code.
I have two columns in table and values I need to consider is of column A.
Also, I need to consider filtered values based on Column B (which we will do manually)
And there are 'n' number of values, the macro "Pricing" should run for each filtered Value from table.
The "Pricing" macro is calling 12 macros.

Sub RunMacrosBasedOnTable()
Dim ws As Worksheet
Dim cellValue As Variant
Dim i As Integer

' Set the worksheet where your table is located
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name

' Assuming your table starts from cell A1 and has one column
For i = 1 To ws.ListObjects("TableName").ListRows.Count
' Read cell value from the table
cellValue = ws.ListObjects("TableName").ListColumns(1).DataBodyRange(i, 1).Value

' Run the corresponding macro based on the cell value
Select Case cellValue
Case "Value1"
Call Pricing

End Select
Next i
End Sub

Check this and let me know

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("TableName").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("TableName").AutoFilter Is Nothing Then
            If ws.ListObjects("TableName").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("TableName").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Value1"
                        Call Pricing
                        ' Add calls to other macros within Pricing
                        Call Macro1
                        Call Macro2
                        ' ... add more calls as needed
                End Select
            End If
        End If
    Next i
End Sub

This assumes that you have applied a filter in Column B of your table. If there's a specific condition or criteria for applying the filter in Column B, you might need to adjust that part accordingly.Remember to replace "Sheet1" and "TableName" with your actual sheet name and table name. If you

 

[ShanShami]

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("Table2").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then
            If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Value1"
                        Call Pricing
                        ' Add calls to other macros within Pricing
                        Call PT1_OD
                        Call PT1_POS
                        Call PT1_Cabin
                        Call PT2_OD
                        Call PT2_POS
                        Call PT2_Cabin
                        Call PT3_OD
                        Call PT3_POS
                        Call PT3_Cabin
                        Call PT4_OD
                        Call PT5_OD
                        Call PT6_OD
                        Call PT7_Cabin
                        Call PT7_Origin
                        Call PT8_Cabin
                        Call PT8_Destination
                        Call PT9_OD
                        Call PT9_ODCabin
                        Call SheetCopy

                End Select
            End If
        End If
    Next i
End Sub

Sub Pricing()
Call PT1_OD
Call PT1_POS
Call PT1_Cabin
Call PT2_OD
Call PT2_POS
Call PT2_Cabin
Call PT3_OD
Call PT3_POS
Call PT3_Cabin
Call PT4_OD
Call PT5_OD
Call PT6_OD
Call PT7_Cabin
Call PT7_Origin
Call PT8_Cabin
Call PT8_Destination
Call PT9_OD
Call PT9_ODCabin
Call SheetCopy
End Sub


Sub PT1_OD()
    Dim filtvalues As Variant, aItm As Variant


'here I have added all 11 macro

 

[Monty]

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

 Sub RunMacrosBasedOnTable() Dim ws As Worksheet Dim cellValue As Variant Dim i As Integer ' Set the worksheet where your table is located Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name ' Assuming your table starts from cell A1 and has two columns (A and B) For i = 1 To ws.ListObjects("Table2").ListRows.Count ' Check if the filter is applied in Column B If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B ' Read cell value from the table cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value ' Run the corresponding macro based on the cell value Select Case cellValue Case "Value1" Call Pricing ' Add calls to other macros within Pricing Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Select End If End If Next i End Sub Sub Pricing() Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Sub Sub PT1_OD() Dim filtvalues As Variant, aItm As Variant 'here I have added all 11 macro

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

 Sub RunMacrosBasedOnTable() Dim ws As Worksheet Dim cellValue As Variant Dim i As Integer ' Set the worksheet where your table is located Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name ' Assuming your table starts from cell A1 and has two columns (A and B) For i = 1 To ws.ListObjects("Table2").ListRows.Count ' Check if the filter is applied in Column B If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B ' Read cell value from the table cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value ' Run the corresponding macro based on the cell value Select Case cellValue Case "Value1" Call Pricing ' Add calls to other macros within Pricing Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Select End If End If Next i End Sub Sub Pricing() Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Sub Sub PT1_OD() Dim filtvalues As Variant, aItm As Variant 'here I have added all 11 macro

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

 Sub RunMacrosBasedOnTable() Dim ws As Worksheet Dim cellValue As Variant Dim i As Integer ' Set the worksheet where your table is located Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name ' Assuming your table starts from cell A1 and has two columns (A and B) For i = 1 To ws.ListObjects("Table2").ListRows.Count ' Check if the filter is applied in Column B If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B ' Read cell value from the table cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value ' Run the corresponding macro based on the cell value Select Case cellValue Case "Value1" Call Pricing ' Add calls to other macros within Pricing Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Select End If End If Next i End Sub Sub Pricing() Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Sub Sub PT1_OD() Dim filtvalues As Variant, aItm As Variant 'here I have added all 11 macro

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

 Sub RunMacrosBasedOnTable() Dim ws As Worksheet Dim cellValue As Variant Dim i As Integer ' Set the worksheet where your table is located Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name ' Assuming your table starts from cell A1 and has two columns (A and B) For i = 1 To ws.ListObjects("Table2").ListRows.Count ' Check if the filter is applied in Column B If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B ' Read cell value from the table cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value ' Run the corresponding macro based on the cell value Select Case cellValue Case "Value1" Call Pricing ' Add calls to other macros within Pricing Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Select End If End If Next i End Sub Sub Pricing() Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Sub Sub PT1_OD() Dim filtvalues As Variant, aItm As Variant 'here I have added all 11 macro

Thank you, I made necessary changes but the code is not triggering the "Pricing" macro which has 11 macro under it.

 Sub RunMacrosBasedOnTable() Dim ws As Worksheet Dim cellValue As Variant Dim i As Integer ' Set the worksheet where your table is located Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name ' Assuming your table starts from cell A1 and has two columns (A and B) For i = 1 To ws.ListObjects("Table2").ListRows.Count ' Check if the filter is applied in Column B If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B ' Read cell value from the table cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value ' Run the corresponding macro based on the cell value Select Case cellValue Case "Value1" Call Pricing ' Add calls to other macros within Pricing Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Select End If End If Next i End Sub Sub Pricing() Call PT1_OD Call PT1_POS Call PT1_Cabin Call PT2_OD Call PT2_POS Call PT2_Cabin Call PT3_OD Call PT3_POS Call PT3_Cabin Call PT4_OD Call PT5_OD Call PT6_OD Call PT7_Cabin Call PT7_Origin Call PT8_Cabin Call PT8_Destination Call PT9_OD Call PT9_ODCabin Call SheetCopy End Sub Sub PT1_OD() Dim filtvalues As Variant, aItm As Variant 'here I have added all 11 macro

Modified code

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("Table2").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then
            If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Value1"
                        Call Pricing
                End Select
            End If
        End If
    Next i
End Sub

 

[ShanShami]

Thank you, but still code is not triggering Call Pricing macro.
There is no error. Am I doing something wrong.

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("Table2").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then
            If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Value1"
                        Call Pricing
                End Select
            End If
        End If
    Next i
End Sub
Sub Pricing()
Call PT1_OD
Call PT1_POS
Call PT1_Cabin
Call PT2_OD
Call PT2_POS
Call PT2_Cabin
Call PT3_OD
Call PT3_POS
Call PT3_Cabin
Call PT4_OD
Call PT5_OD
Call PT6_OD
Call PT7_Cabin
Call PT7_Origin
Call PT8_Cabin
Call PT8_Destination
Call PT9_OD
Call PT9_ODCabin
Call SheetCopy
End Sub


Sub PT1_OD()
    Dim filtvalues As Variant, aItm As Variant
    Dim pt As PivotTable
    Dim PF As PivotField
    Dim tmpFltr As String, strFltr As String, arrFltr As Variant

' I have added all 11 macros here

 

[vletm]

Monty​

Is there any reason why did You pasted five times same part of code in #7 reply?

 

[vletm]

ShanShami​

What is variable cellValue's value?

Select Case cellValue

Can You verify that it really is "Value1"?
... if it is something else then ... it won't run Pricing

 

[ShanShami]

Monty​

Is there any reason why did You pasted five times same part of code in #7 reply?

Sorry.. But I am not sure why it is pasted 7 times

Moderator note: This question is for @Monty .

 

[ShanShami]

ShanShami​

What is variable cellValue's value?

Select Case cellValue

Can You verify that it really is "Value1"?
... if it is something else then ... it won't run Pricing

Is value1 is the filtered value of Column A?
Sorry I am didn't understand. I thought code will pick the filtered Value from table one by one.
Below table values are for your reference. There are n number of values.

OD	KUR
KUR-AMM	BOM
KUR-BAH	KUR
KUR-CAI	KUR
KUR-CDG	KUR
KUR-DAR	KUR
KUR-DMM	KUR
KUR-DOH	KUR
KUR-DXB	KUR
KUR-FRA	KUR
KUR-IST	KUR
KUR-JED	KUR
KUR-KWI	KUR
KUR-LHR	KUR
KUR-MCT	KUR
KUR-MED	KUR
KUR-MUC	KUR
KUR-MXP	KUR
KUR-RUH	KUR
KUR-SLL	KUR
KUR-SVO	KUR
KUR-ZNZ	KUR
KUR-ZRH	KUR

 

[vletm]

ShanShami​

Based: What is variable cellValue's value?
Okay ...
You're using #2 Reply's -code.
There is only one fixed value for that variable "Value1", which isn't in Your above list.
... then ... it won't run Pricing
I won't or cannot start guessing based Your given details.
You should able to send a sample Excel-file, which has some sample data as in Your real file.

 

[Monty]

Is value1 is the filtered value of Column A?
Sorry I am didn't understand. I thought code will pick the filtered Value from table one by one.
Below table values are for your reference. There are n number of values.

OD	KUR
KUR-AMM	BOM
KUR-BAH	KUR
KUR-CAI	KUR
KUR-CDG	KUR
KUR-DAR	KUR
KUR-DMM	KUR
KUR-DOH	KUR
KUR-DXB	KUR
KUR-FRA	KUR
KUR-IST	KUR
KUR-JED	KUR
KUR-KWI	KUR
KUR-LHR	KUR
KUR-MCT	KUR
KUR-MED	KUR
KUR-MUC	KUR
KUR-MXP	KUR
KUR-RUH	KUR
KUR-SLL	KUR
KUR-SVO	KUR
KUR-ZNZ	KUR
KUR-ZRH	KUR

I did some tweek...lets see

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("Table2").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then
            If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Pricing", "PT1_OD", "PT1_POS", "PT1_Cabin", "PT2_OD", "PT2_POS", "PT2_Cabin", _
                         "PT3_OD", "PT3_POS", "PT3_Cabin", "PT4_OD", "PT5_OD", "PT6_OD", _
                         "PT7_Cabin", "PT7_Origin", "PT8_Cabin", "PT8_Destination", _
                         "PT9_OD", "PT9_ODCabin", "SheetCopy"
                        Call Pricing
                End Select
            End If
        End If
    Next i
End Sub

 

[ShanShami]

I did some tweek...lets see

Sub RunMacrosBasedOnTable()
    Dim ws As Worksheet
    Dim cellValue As Variant
    Dim i As Integer

    ' Set the worksheet where your table is located
    Set ws = ThisWorkbook.Sheets("OD_POS") ' Change "Sheet1" to your actual sheet name

    ' Assuming your table starts from cell A1 and has two columns (A and B)
    For i = 1 To ws.ListObjects("Table2").ListRows.Count
        ' Check if the filter is applied in Column B
        If Not ws.ListObjects("Table2").AutoFilter Is Nothing Then
            If ws.ListObjects("Table2").AutoFilter.Filters(2).Count > 1 Then ' Assuming filter is applied on Column B
                ' Read cell value from the table
                cellValue = ws.ListObjects("Table2").ListColumns(1).DataBodyRange(i, 1).Value

                ' Run the corresponding macro based on the cell value
                Select Case cellValue
                    Case "Pricing", "PT1_OD", "PT1_POS", "PT1_Cabin", "PT2_OD", "PT2_POS", "PT2_Cabin", _
                         "PT3_OD", "PT3_POS", "PT3_Cabin", "PT4_OD", "PT5_OD", "PT6_OD", _
                         "PT7_Cabin", "PT7_Origin", "PT8_Cabin", "PT8_Destination", _
                         "PT9_OD", "PT9_ODCabin", "SheetCopy"
                        Call Pricing
                End Select
            End If
        End If
    Next i
End Sub

getting below error

 

[ShanShami]

I am still not clear about Values ....? If you can please assist me to understand.

with regards to sample data - data is very huge....
the 11 macros are working based on cell value manually entered in specific cell i.e. C3 under sheet name "Report". I think because of this code is not triggering the Pricing code.

Can we change C3 (under sheet name "Report") value based on the filtered values of Table one by one which, I believe trigger the macro "Pricing"

e.g. code should pick up first filtered value (e.g. KUR-AMM) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
Once all 11 macro ran then pick up next value (e.g. KUR-BAH) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
again once 11 macro ran then pick up next value (e.g. KUR-CAI) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
This process should continue until all filtered rows are completed.

KUR-AMM	KUR
KUR-BAH	KUR
KUR-CAI	KUR

 

[vletm]

ShanShami​

If You refer to my above (#16 reply) then
I have asked a sample data ... which means 10-20 rows data ... it cannot be huge.
... in that sample Excel-file have to have all needed sheets ... codes.
Without that - it's a challenge to test and verify Your case; seems that even 'AI' cannot neither do it.

 

[ShanShami]

OK.. Let me built a sample data and send it to you.

 

[ShanShami]

Here the sample files.
Please keep the "Fare" workbook open as last macro is copying data to this workbook.
For sample purpose I have not taken all 11 macros but a few macros considered.

Can we change C3 (under sheet name "Report") value based on the filtered values of Table one by one which, I believe trigger the macro "Pricing"

e.g. code should pick up first filtered value (e.g. KUR-AMM) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
Once all 11 macro ran then pick up next value (e.g. KUR-BAH) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
again once 11 macro ran then pick up next value (e.g. KUR-CAI) of table and copy it to C3 (under sheet name "Report") and then run all 11 macros.
This process should continue until all filtered rows are completed.

 

[Marc L]

Hello, C3 ?‼ ​

​

According to your attachment a VBA demonstration for starters (replace Macro1 & Macro10 with all VBA procedures to launch) :​

Sub Demo1()
   With Sheet3.[_FilterDatabase].Rows
    For R& = 2 To .Count
     If Not .Item(R).Hidden Then
        Sheet4.[C2] = .Cells(R, 1)
        Macro1
        Macro10
     End If
    Next
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[ShanShami]

Its is C2 in "Report" sheet which needs to be changed based on the filtered value.
I am sorry, but I didn't understand what what you would like to mention.

 

[ShanShami]

Understood.. I have updated the code as below but when I am using the code in actual file, I am getting error as "Run Error 424 / Object required". Error is coming for "With Sheet3.[_FilterDatabase].Rows".
Can you please explain me the code i.e. which sheet is considered as Sheet3 and Sheet4

Sub Demo1()
   With Sheet3.[_FilterDatabase].Rows
    For R& = 2 To .Count
     If Not .Item(R).Hidden Then
        Sheet4.[C2] = .Cells(R, 1)
Call PT1_OD
Call PT1_POS
Call PT1_Cabin
Call PT2_OD
Call PT2_POS
Call PT2_Cabin
Call PT3_OD
Call PT3_POS
Call PT3_Cabin
Call PT4_OD
Call PT5_OD
Call PT6_OD
Call PT7_Cabin
Call PT7_Origin
Call PT8_Cabin
Call PT8_Destination
Call PT9_OD
Call PT9_ODCabin
Call SheetCopy
     End If
    Next
   End With
End Sub

 

[Marc L]

As mentionned my demonstration works with your post #19 attachment, did you try with the same workbooks at least ?​

Sheet3 & Sheet4 are the Sample_File.xlsm worksheets CodeNames on VBE side like you can see yourself …​

​

 

[ShanShami]

Yes.. I was suppose to msg.
I understood the code. And working as desired for in Sample file as well as main file.
Thank you so much.. Appreciated!