subtotal a dynamic range with vba

[vba_learner]

I have a spreadsheet with multiple columns. I need to subtotal the range. I m faced with a problem,as the column in which my figures for subtotal are,keeps changing monthly. I need help with a cha code to carry out subtotal based on column header. So for example if column A has sales,with header "Sales", and colum B has names of sales reps with column header "Names"code should search for a specific user using auto filter and subtotal the visible range that is below the sales header.
Basically I have to total sales figures for sales reps and the names and sales figures columns keep changing each month.
Please help,I hope I m clear in what I m looking for. Thanks for ur help in advance.

 

[NARAYANK991]

Hi ,

Please note that in general , if you upload either your working file , or at least a sample file , you are likely to get a solution faster ; this is even more so , where you are looking for code ; even if someone would like to help you out with the code , it is less likely that they will generate the test data , needed to test the code thoroughly , on their own. If you can upload your file , you save them the trouble , and in the process are helping yourself to get a solution faster.

Narayan

 

[vba_learner]

Thank u for ur help,I will upload my sample file,plz accept my apologies as this is the first time I have ever posted something on a forum.
Regards.

 

[vba_learner]

Hi There,
Please see the sample workbook, as you will notice, in sheet 1 the two columns are A & B, the columns change in sheet 2, and change further in sheet 3. What I would like to do is filter based on Sales REP and then subtotal visible range for a Sales REP.
The issue is, if the column is fixed, I have a code that does the work, but if the Column changes the code displays 0 result as the column it is trying to subtotal has no figures in it. I have also pasted a generic code that currently works on a known column

ActiveSheet.Range("A1:B1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="CELL REF"

ActiveSheet.Range("E4").Select

Set MYRANGE = ActiveSheet.Range("B2:B65000")
ActiveWorkbook.ActiveSheet.Range("E4") = _
Application.WorksheetFunction.Subtotal(109, MYRANGE)
ActiveSheet.Range("A1:B1").Select
Selection.AutoFilter

I hope this will help.
Thank you for your help in this regards.

 

[NARAYANK991]

Hi ,

In your initial post , you have mentioned :

code should search for a specific user using auto filter and subtotal the visible range that is below the sales header.
Basically I have to total sales figures for sales reps and the names and sales figures columns keep changing each month.

How and where will you specify this user ?

Secondly , will all your sheets have data in only these two columns ? If the Sales Rep column and the Yield column are far apart , will the columns in between have any data or will they be blank ?

Narayan

 

[vba_learner]

Ideally within the program itself
So the first line of code will have to be a search for column header "Sales REP"
once the code finds the Header Then it would apply the filter to that column with specific sales REP names

The Columns are sometimes next to each other, and other times are far apart, in some instances there is no data in the columns in between and in some , there is other data that is usually not needed (Customer ratings, Address, Quantity etc.).

The only problem I have is to locate the 2 columns, Sales REP to apply filter and then subsequently the sales figure column to subtotal that range.

I tried somthing along the lines of
activesheet.cell.search (The usual search function that can be done by ctl+F), and in that the Lookfor value is "Sales REP"
once this is run, it activates the cell that has Header "Sales REP", from that point I have my code for filter

selection.Autofilter
Selection.AutoFilter Field:=2, Criteria1:="CELL REF" (To locate sales REP, John,Sandra)

it searches for the sales REP and applies the filter also, the next part is where I am really stuck to look for Sales Figure column to subtotal the visible range.

Hope this will clarify the issue.

Thank you for your support.

 

[SirJB7]

Hi, vba_learner!

In this code:

Option Explicit

Sub FilterAndSumByHeaders()
    ' constants
    Const kiTitle = 1
    Const ksFilter = "Sales rep"
    Const ksSum = "Yeild"
    ' declarations
    Dim ws As Worksheet, rng As Range
    Dim I As Integer, J As Integer, K As Integer
    ' start
    For I = 1 To Worksheets.Count
        Set ws = Worksheets(I)
        With ws
            With .Rows(kiTitle)
                ' header filter
                Set rng = .Find(ksFilter, .Cells(kiTitle, 1), xlValues, xlWhole)
                If rng Is Nothing Then
                    MsgBox "Column header '" & ksFilter & "' not found for worksheet '" & _
                        ws.Name & "'", vbApplicationModal + vbCritical + vbOKOnly, "Error"
                Else
                    J = rng.Column
                End If
                ' header sum
                Set rng = .Find(ksSum, .Cells(kiTitle, 1), xlValues, xlWhole)
                If rng Is Nothing Then
                    MsgBox "Column header '" & ksSum & "' not found for worksheet '" & _
                        ws.Name & "'", vbApplicationModal + vbCritical + vbOKOnly, "Error"
                Else
                    K = rng.Column
                End If
            End With
            ' filter
            If J * K > 0 Then
                MsgBox "Worksheet: " & I & " (" & .Name & ")" & vbCr & _
                    "'" & ksFilter & "' at column " & J & vbCr & _
                    "'" & ksSum & "' at column " & K, _
                    vbApplicationModal + vbInformation + vbOKOnly, "Display"
                ' here apply the filter, sum,... do your stuff
            End If
            ' sum
            If MsgBox("Proceed to next worksheet (Yes) or cancel (Cancel)?", _
                vbApplicationModal + vbInformation + vbDefaultButton1 + vbOKCancel, _
                "Control") = vbCancel Then
                    Exit For
            End If
        End With
    Next I
    ' end
    Set rng = Nothing
    Set ws = Nothing
    Beep
End Sub

... you'll find a template for searching 2 column headers in all worksheets of a workbook. The only constraints are that:
- title row is the same in all worksheets (specified in constant kiTitle)
- row headers are the same in all worksheets (specified in constants ksFilter and ksSum)

Then you should place your stuff for each worksheet after this line:

                ' here apply the filter, sum,... do your stuff

taking care that variable J holds the column number for ksFilter column and K does the same for ksSum.

Just advise if any issue.

Regards!

PS: Just in case, you should place this code into a standard module.