Hello, I'm new to VBA and thank you in advance.
Macro: In short, this macro will execute another macro when the wanted cell is active.
Problem: This macro works fine till column X adding more after that produces the "procedure too large" error. I think it has reached its capacity limit. How do I make my sausage code shorter/work?
Note: this same code continues till AA-column and the only thing that changes are the range columns ("B11"->"C11->D11") and code (B_11 -> C_11 -> D_11).
Description of the pictures and codes. Picture 1: In the picture columns B:AA is areas and rows 11:14 are tasks. The code I have below calls different macros set for those cells. Example cell B11 calls for B_11 macro and so goes on.
Select area&task picture
` continues till range("AA11")... call AA_11
Picture 2: After clicking for example cell B11 the below macro that is named B_11 will be active. The purpose of this macro is to filter mass data to the wanted area and task. So column B = Area 082M and Row 11 = Tasks 1. In the datasheet, areas are set in rows and tasks are in table headlines so to filter tasks I need to hide unnecessary columns.
after Cell B11 activates and runs B_11 macro
Macro: In short, this macro will execute another macro when the wanted cell is active.
Problem: This macro works fine till column X adding more after that produces the "procedure too large" error. I think it has reached its capacity limit. How do I make my sausage code shorter/work?
Note: this same code continues till AA-column and the only thing that changes are the range columns ("B11"->"C11->D11") and code (B_11 -> C_11 -> D_11).
Description of the pictures and codes. Picture 1: In the picture columns B:AA is areas and rows 11:14 are tasks. The code I have below calls different macros set for those cells. Example cell B11 calls for B_11 macro and so goes on.
Select area&task picture
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
' B-Column Click Macro------------------------------------------------------------
If Not Intersect(Target, Range("B11")) Is Nothing Then Call B_11
If Not Intersect(Target, Range("B12")) Is Nothing Then Call B_12
If Not Intersect(Target, Range("B13")) Is Nothing Then Call B_13
If Not Intersect(Target, Range("B14")) Is Nothing Then Call B_14
If Not Intersect(Target, Range("B16")) Is Nothing Then Call B_16
If Not Intersect(Target, Range("B17")) Is Nothing Then Call B_17
If Not Intersect(Target, Range("B19")) Is Nothing Then Call B_19
If Not Intersect(Target, Range("B20")) Is Nothing Then Call B_20
If Not Intersect(Target, Range("B21")) Is Nothing Then Call B_21
If Not Intersect(Target, Range("B22")) Is Nothing Then Call B_22
If Not Intersect(Target, Range("B24")) Is Nothing Then Call B_24
If Not Intersect(Target, Range("B25")) Is Nothing Then Call B_25
If Not Intersect(Target, Range("B26")) Is Nothing Then Call B_26
If Not Intersect(Target, Range("B27")) Is Nothing Then Call B_27
' C-Column Click Macro------------------------------------------------------------
If Not Intersect(Target, Range("C11")) Is Nothing Then Call C_11
If Not Intersect(Target, Range("C12")) Is Nothing Then Call C_12
If Not Intersect(Target, Range("C13")) Is Nothing Then Call C_13
If Not Intersect(Target, Range("C14")) Is Nothing Then Call C_14
If Not Intersect(Target, Range("C16")) Is Nothing Then Call C_16
If Not Intersect(Target, Range("C17")) Is Nothing Then Call C_17
If Not Intersect(Target, Range("C19")) Is Nothing Then Call C_19
If Not Intersect(Target, Range("C20")) Is Nothing Then Call C_20
If Not Intersect(Target, Range("C21")) Is Nothing Then Call C_21
If Not Intersect(Target, Range("C22")) Is Nothing Then Call C_22
If Not Intersect(Target, Range("C24")) Is Nothing Then Call C_24
If Not Intersect(Target, Range("C25")) Is Nothing Then Call C_25
If Not Intersect(Target, Range("C26")) Is Nothing Then Call C_26
If Not Intersect(Target, Range("C27")) Is Nothing Then Call C_27
` continues till range("AA11")... call AA_11
Picture 2: After clicking for example cell B11 the below macro that is named B_11 will be active. The purpose of this macro is to filter mass data to the wanted area and task. So column B = Area 082M and Row 11 = Tasks 1. In the datasheet, areas are set in rows and tasks are in table headlines so to filter tasks I need to hide unnecessary columns.
after Cell B11 activates and runs B_11 macro
Code:
Sub B_11()
'
' Area-082M
Sheets("Hyttityöt").Select
' hiding unnecessary columns
Columns("F:BI").Hidden = False
Columns("J:BI").Hidden = True
' filter data to only area 082M
ActiveSheet.ListObjects("Table2435").Range.AutoFilter Field:=4, Criteria1:= _
"082M"
End Sub
Attachments
Last edited: