Macro to generate a report, select next department from list and recalc

[VEI]

I was wondering if it is possible to have a macro that would select a department from a drop down list, recalculate the sheet, print and then select the next department in the list and start the process over again. This would continue until it reached the end of the list.

This would produce an income statement for each department based on the department selected.

I have searched and I have not found anything that I thought applied. Thank you in advance for your expertise.

 

[jbeaucaire]

This should do it, just edit the MyCell to the cell with your drop down list.

Option Explicit

Sub CycleThroughDataValidationOptions()
'Summary:   Cycle through all the options in a drop down for a specific cell
Dim MyCell As Range, myDVList As String
Dim DVCnt As Long, Itm As Long

'set the cell address here
    Set MyCell = Range("B1")
'retrieve the DV source range from the cell
    myDVList = Mid(MyCell.Validation.Formula1, 2, 99)
'count the items in that list
    DVCnt = Range(myDVList).Cells.Count

'cycle through the items one at a time
    For Itm = 1 To DVCnt
        MyCell = Range(myDVList).Cells(Itm)
        ActiveSheet.PrintOut
    Next Itm

End Sub

 

[VEI]

You make this look so easy...

I need a manual recalculation after each department is selected.

Is it possible to have a way to stop the macro if you do not want it to go through the whole list?

How about the ability to select a starting and stoping point on the data validation list?

I am taking a 3 day course on VBA locally so hopefully that will get me up to speed quickly.

Thanks again for your expertise.

 

[jbeaucaire]

Perhaps add this:

    For Itm = 1 To DVCnt
        MyCell = Range(myDVList).Cells(Itm)
        ActiveSheet.Calculate
        ActiveSheet.PrintOut
    Next Itm

As for starting and ending points... ugh. How long is that list?

 

[VEI]

Approximately 300 items in the list. This is just a wish list if not easily done. I think a work around would be to just modify the data validation list. Any way to stop the macro once it is started in case I need to stop it?

 

[jbeaucaire]

What if you add an additional cell to your sheet that use the same DV list, then select your FIRST and LAST categories in those two cells? If B1 is the first cell, let's make C1 the second cell, and it's optional, so if nothing is in there it will continue to the end of the list from the starting point in B1. If B1 is empty, too, it will simply process all the categories.

Option Explicit

Sub CycleThroughDataValidationOptions()
'Author:    Jerry Beaucaire,  8/25/2011
'Summary:   Cycle through all the options in a drop down for a specific cell
Dim MyCell As Range, myDVList As String, strVal As String
Dim DVCnt As Long, Itm As Long
Dim Frst As Long, Last As Long

With ActiveSheet
'set the primary cell address here
    Set MyCell = .Range("B2")
'retrieve the DV source range from the cell
    myDVList = Mid(MyCell.Validation.Formula1, 2, 99)

'First and last
    On Error Resume Next
    'strVal = MyCell.Value
    Frst = WorksheetFunction.Match(MyCell, Range(myDVList), 0)
    If Frst = 0 Then Frst = 1
    'strVal = MyCell.Offset(, 1).Value
    Last = WorksheetFunction.Match(MyCell.Offset(, 1), Range(myDVList), 0)
    If Last = 0 Then Last = Range(myDVList).Cells.Count

'cycle through the items one at a time
    For Itm = Frst To Last
        MyCell = Range(myDVList).Cells(Itm)
        .Calculate
        .PrintOut
    Next Itm
End With

End Sub