Macro to Run Through List of Variables

Question

I have a macro that runs a series of other macros. The macro runs automatically when I change the value of a specific cell (B1). The macro generates a project report for the project number specified in B1. I have about 75 active projects at any given time.

What I'd like to do is add the list of projects to the workbook and have it run through the each value. The values and number of values change each week, so I don't want to hard code the values into the macro. I'm assuming I'd use the loop function, but not certain how to go about it.

My code for the current macro is:

Sub AA_RunAll()

Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
DoEvents
Call OpenFile
Call Copy1
Call DeleteRows1
Call DeleteRows2
Call DetailCopy
Call RemoveEmptySheets
Call SummarySheet

Workbooks("Project WIP Template.xlsm").Worksheets("Summary").Activate
Application.Goto Range("A1"), True
Application.ScreenUpdating = True
Application.DisplayAlerts = True


Call SaveAs

Workbooks("Project WIP V5.xlsm").Worksheets("Summary").Activate
Range("b1").Select

MsgBox "Report Complete"

End Sub

Thanks in advance

This question isn't very clear. How are these various macros (DetailCopy etc.) supposed to get these unspecified "values" that your question alludes to? You seem to know that you need some sort of loop. Given the lack of details it is hard to say anything more than that. Perhaps you need to rewrite your macros so that they take parameters, then call the macros in a loop, passing them the values one by one. — John Coleman
– John Coleman, Commented Oct 10, 2017 at 18:23
A few questions: Where is the project list taken from (what generates this list), what information is being associated with this list, are there duplicates in the project list (versioning, etc), and what is the intention of the loop? Specifically, you have a list, you're looping through them, but for what outcome/output (project report is personal to you, but not to us)? — Cyril
– Cyril, Commented Oct 10, 2017 at 18:46
The project list is currently stored in a separate workbook, but could be added into the existing one. There are no duplicate values for the project list. In terms of the loop, that may not be the correct function, just my assuming. Essentially, right now I am manually entering the project number is B1. As soon as I hit enter, it runs the macro, generates my project report, saves and closes it and brings me back to the template where I can enter another project number. — acoatta
– acoatta, Commented Oct 10, 2017 at 19:18
I'd like to have it follow the exact same process, but it instead of me manually entering the project into B1, have it run through the list of projects. — acoatta
– acoatta, Commented Oct 10, 2017 at 19:22

Matteo NNZ · Accepted Answer · 2017-10-10 19:45:14Z

0

If your macro is ran each time that you modify the value of Range("B1"), I guess you're firing the Worksheet_Change event testing for:

If Target.Address = "$B$1" Then
    AA_RunAll '<-- call your macro
End If

If that's the case, the easiest way you have to trick your own design is to write the list of project numbers in a spreadsheet, say Project numbers, column A:

1
2
3
...

... and then loop through this list to replace the value of your cell B1:

Sub allProjects()
    For j = 1 To Sheets("Project numbers").Range("A1").End(xlDown).Row
        Range("B1").Value = Sheets("Project numbers").Range("A" & j).Value
        'the above set will call the worksheet change and run your macro for a given value
    Next j
End Sub

I must say, though, that the design is not ideal. I would rather make your macro take a parameter:

Sub AA_RunAll(ByVal projectNumber As Integer)
    'your code, pass the projectNumber to other submacros if needed
End Sub

... and call the macro programmatically with the correct number, instead of tricking the Excel sheet to fire the Worksheet_Change event.

answered Oct 10, 2017 at 19:45

Matteo NNZ

12.8k13 gold badges62 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

acoatta Over a year ago

Thanks @Matteo NNZ You are correct. The current code to trigger the macro on cell change is using Worksheet_Change event. I'm relatively new to writing macros and most of what I've done has been from reading this forum. Can you elaborate on the Parameter recommendation? Thanks Aaron

Collectives™ on Stack Overflow

Macro to Run Through List of Variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related