Consolidator macro too long. Need a better way.

[sherrie7]

XP, Excel 2010

I do community bookkeeping and I am a beginner at macros. This forum has been an incredible help! I try to find answers on my own, but this is WAY beyond me.

A volunteer here helped me with a consolidator code, but now it is too long.
It worked when I only had 400 clients to call. For example, I run the consolidator sub macro for client 399 and I get all the rows that have "399" in the first cell. That way I can see all of member 399's transactions in my accounting.

I also found on this forum a "AddToolbar" macro to call the sub from the consolidator macro. It created an "Add In" combo box and I could click which member I wanted so I didn't have to have 400 buttons. Again worked great till I had to add more members.

If anyone has time to help, I would greatly appreciate it!

Here is the consolidator code only for members 100 - 110. I have total 800 members.
You know why I get the error "Compile error: Procedure too large"!

Option Explicit

Const strShtCmn As String = "C"

Private Sub Consolidator(SheetName As String, RowNo As Long, Match As String)
    Dim sh As Worksheet
    Dim r As Object, j As Long
    
    Dim cs As Worksheet
    
    ActiveWindow.ScrollRow = RowNo - 3
    Application.ScreenUpdating = False
        
    Set cs = ThisWorkbook.Worksheets(SheetName)
    
    For Each sh In ThisWorkbook.Worksheets
        Select Case sh.Name
            Case "P", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"
                For Each r In sh.UsedRange.Rows
                    If UCase(r.Cells(1)) = Match Then
                        For j = 1 To 88
                            cs.Cells(RowNo, j) = r.Cells(j)
                        Next j
                    RowNo = RowNo + 1
                    End If
                Next r
        End Select
    Next sh
    
    cs.Range("A" & RowNo).Select
    
    Set cs = Nothing
    
    Application.ScreenUpdating = True

End Sub
Private Sub Consolidator_100()
    Consolidator strShtCmn, 7, "100"
End Sub
Private Sub Consolidator_101()
    Consolidator strShtCmn, 7, "101"
End Sub
Private Sub Consolidator_102()
    Consolidator strShtCmn, 7, "102"
End Sub
Private Sub Consolidator_103()
    Consolidator strShtCmn, 7, "103"
End Sub
Private Sub Consolidator_104()
    Consolidator strShtCmn, 7, "104"
End Sub
Private Sub Consolidator_105()
    Consolidator strShtCmn, 7, "105"
End Sub
Private Sub Consolidator_106()
    Consolidator strShtCmn, 7, "106"
End Sub
Private Sub Consolidator_107()
    Consolidator strShtCmn, 7, "107"
End Sub
Private Sub Consolidator_108()
    Consolidator strShtCmn, 7, "108"
End Sub
Private Sub Consolidator_109()
    Consolidator strShtCmn, 7, "109"
End Sub
Private Sub Consolidator_110()
    Consolidator strShtCmn, 7, "110"
End Sub

You can see I am bringing all the rows from the worksheets "P"..."12" that have "100" (or which ever member number I want) in the first cell to worksheet "C".
This is a great code even for beginners, but I know there is a cleaner way to do it since I have so many members.

Thank you for any help!

 

[Rasm]

Replace all the Consolidator_??? with code below

before you call the sub - set the variable

ConSolNum=100 or 101 or w/e the number is

when you call the sub using something like this

Test=Consolidator(ConSolNum) 'Now it passes the
ConSolNum the procedure


This assumes the only diff in these many procedures is simply the varaible ConSolNum

Hope this works for you-make sure to make back up - hehehehe

Private Sub Consolidator(ConSolNum)
    Consolidator strShtCmn, 7, ConSolNum
End Sub

 

[sherrie7]

Hola Rasm, Thank you for your help!!! I have to go out and will try it as soon as I return. Can't wait. Will let you know how I do.

 

[shg]

How are all these Consilidator subs being called?

 

[Rasm]

Its like Shg says - how are the procudres called - you need to pass the variable as part of the call - good luck

 

[sherrie7]

Thank you all for trying to help, but I just don't know enough to understand. I will try to tell you how I did this before I got too many members. It quit working when I got over 400.

I used the "addtoolbar" macro that jonmo1 showed someone on this forum http://www.mrexcel.com/forum/showthread.php?t=298141&highlight=addtoolbar

It opens that toolbar with a dropdown box listing the subs.
Here is an example of the "addtoolbar" I use.
I do not understand exactly how to use a variable. That is what I need to somehow enter a member's number and retrieve the rows without having to have all this code for members 100 thru 800 members and using the "addtoolbar"

This is the toolbar code I used:

Sub AddToolBar()
Application.ScreenUpdating = False
Dim mybar, newcombo1

On Error GoTo Term
Set mybar = CommandBars.Add(Name:=ThisWorkbook.Name, Position:=msoBarTop, Temporary:=True)
mybar.Visible = True

Set newcombo1 = mybar.Controls.Add(Type:=msoControlComboBox)
With newcombo1
    .AddItem "100"
    .AddItem "101"
    .AddItem "102"
    .AddItem "103"

    .BeginGroup = False
    .Text = "Macros"
    .Caption = "Select Macro"
    .Style = msoComboLabel
    .TooltipText = "Choose A Macro To Run."
    .OnAction = "ToolBarControls1"
End With

Term:
Application.ScreenUpdating = True
Exit Sub
Application.ScreenUpdating = True
End Sub


Function ToolBarControls1()

userChoice = CommandBars(ThisWorkbook.Name).Controls(1).ListIndex
    Select Case userChoice
        Case 1
            Application.Run "'2011sec001.xlsm'!Consolidator_100"
        Case 2
            Application.Run "'2011sec001.xlsm'!Consolidator_101"
        Case 3
            Application.Run "'2011sec001.xlsm'!Consolidator_102"
        Case 4
            Application.Run "'2011sec001.xlsm'!Consolidator_103"
        Case Else
            MsgBox ("Invalid choice. Please choose again.")
    End Select
End Function

So I have been using 2 modules. One is the addtoolbar and one is the consolidator I posted earlier.

Sorry, I just don't know that much about macros. I think I understood to replace the code with what you supplied, but I am lost after that...

"before you call the sub - set the variable

ConSolNum=100 or 101 or w/e the number is

when you call the sub using something like this

Test=Consolidator(ConSolNum) 'Now it passes the
ConSolNum the procedure "

I am going to try to wrap my brain around this, but I don't feel lucky...

Thanks again.

 

[Rasm]

Sherrie
Try this-this assumes the items in the controls are "100" "101" and so on - but from your .AddItem function - it looks that way

Function ToolBarControls1()
    Dim ConSolNum
    'userChoice = CommandBars(ThisWorkbook.Name).Controls(1).ListIndex
    With CommandBars(ThisWorkbook.Name).Controls(1)
        ConSolNum = .List(.ListIndex)   'Sherrie check that the the ColSolNum is correct
    End With
    Call Consolidator(ConSolNum)
    
    'Select Case userChoice
    '    Case 1
    '        Application.Run "'2011sec001.xlsm'!Consolidator_100"
    '    Case 2
    '        Application.Run "'2011sec001.xlsm'!Consolidator_101"
    '    Case 3
    '        Application.Run "'2011sec001.xlsm'!Consolidator_102"
    '    Case 4
    '        Application.Run "'2011sec001.xlsm'!Consolidator_103"
    '    Case Else
    '        MsgBox ("Invalid choice. Please choose again.")
    'End Select
End Function
Private Sub Consolidator(ConSolNum)
    Consolidator strShtCmn, 7, ConSolNum
End Sub

 

[sherrie7]

I'm really sorry I don't have the knowledge...if you want to quit on this one. I understand. But this is where I am at.

I had 2 modules:
consolidator
addtoolbar - which has over 2000 lines of code because of 800 members. it creates the dropdown in "Add-Ins" but only works when I have half the members. With all the 800 members, it has the error "Compile error: Procedure too large"

Before I posted here, I divided both modules with 400 each. It worked kindof, but when I used the addtoolbar1 I could not get the addtoolbar2 to open. I had to close the file and reopen to get the addtoolbar 2 to open and vise versa.

Now...
I have the 2 modules, consolidator andaddtoolbar with all 800.
after I saw your last post... I created a 3rd module (toolbarcontrols1 ) with the code you last gave me and adding all 800, but I dont know how to use it. I don't understand what "ColSolNum" is. I tried looking it up, but no luck. I ran addtoolbar and from the drop down choose a number and I get the error "Cannot run the macro ...toolbarcontrols1..."

 

[shg]

Are you doing this for one group or several?

Why not just have the code prompt the user to enter the group(s) to consolidate, and the user could enter one or a comma-delimited string of several?

 

[sherrie7]

I have one group of members that have numbers 100-800.
I can divide it into 2 or 3 groups.
I only need to get one member at a time.
For example, I want to see all rows with "100" in the first cell, I run consolidator_100. I clear the results with a macro and run the next, consolidator_101 or whatever.

"Why not just have the code prompt the user to enter the group(s) to consolidate, and the user could enter one or a comma-delimited string of several?" - not sure how to do this.