Ugly VBA

[VBAMePlease]

Private Sub CommandButton2_Click()If MsgBox("Capacity Payment Structure Up-to-date?", vbQuestion + vbYesNo) <> vbYes Then Run


    Range("D4").Select
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("C16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E16").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False
    
Call Macro Clear_Results


    Range("C17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False
    
Call Macro Clear_Results


    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False
    
Call Macro Clear_Results


    Range("C19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False


Call Macro Clear_Results


    Range("C20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False


Call Macro Clear_Results


    Range("C21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Results").Select
    Range("D21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rate Analysis").Select
    Range("L27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Call Macro Run_BR_Query


        Range("P28").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Results").Select
    Range("E20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Analysis").Select
    Application.CutCopyMode = False

Essentially how can I get this to run all of the values in C19 and D19 down to C125 and D125 through the "Run_BR_Query" macro and copy the results for each iteration into the "Results" sheet in E19...

Happy to provide more context here for anyone who would prefer it.

Thanks.

 

[VBAMePlease]

YIKES! The "Call Run_BR_Query" appears to be nothing more than a lot of copy and paste of values on the Sheet("Rate Analysis"). Is there a reason you can't just use workbook formulas?
eg Cell "E19" set as "=P26" and Cell "P29" and "Q29" set, respectively as "=E27" and "=F27"

The reason I can't set E19 to P26 is that E19 is a driver cell for another model that calculates hourly revenues for a project, and I need to copy P26 (value indicative of a year; "2017"), then I need to copy revenue results from E27 and D30:D41 into P29 and then P30:P41. After I run this, now I need to run the next years estimated revenues. So by copying Q29 into E19, I now change my driver start date for the 8760 model which runs revenues for that year, then I need to copy those results from E27 and D30:D41 into Q29 and then Q30:Q41.

The anticipated revenues for each year vary dependent upon the market I'm targeting (L26 in "Rate Analysis") and the sensitivity case I want to apply to said market (L27 in "Rate Analysis").

This is why I need the macro on the dashboard to essentially run this annual revenue analysis for every market and every sensitivity case so that I can compare and contrast all available markets with all available sensitivities. Essentially, I am taking all 5 of my sensitivity case results and evaluating markets individually. The 5 sensitivity cases allow me to create a weighted average trend line based on subjective reasoning as to what case is more likely to occur in what market.

I utilized a recorded macro for the Run_BR_Query since it was a very straight-forward macro. It might be worth my time to shorten it down to cut down on processing time given the computing ask of this macro.

 

[Beyond_avarice]

Walk through each copy and paste and mimic what I did when I want one cell to equal the value of another.

Eg:

''''This cell is to equal the value that is contained in.................this cell
Worksheets("Rate Analysis").Range("E19").Value = Worksheets("Rate Analysis").Range("P26").Value
Worksheets("Rate Analysis").Range("P29").Value = Worksheets("Rate Analysis").Range("E27").Value
Worksheets("Rate Analysis").Range("Q29").Value = Worksheets("Rate Analysis").Range("F27").Value

I copied your code to a Word doc and did a search for "Selection.PasteSpecial"= 152 (YIKES). That's 914 lines that could be cut down to 1/6th of what it is.(and I still think, if you have to copy and paste that much; you're doing too much work and there has to be a better layout.)

In the end. I still think you should validate your formulas. If you walk through the script and C16:D16.values end up in L26:27.values. Then it's up to your "P28:AN28" to come up with the right calculations. If those are working then those values should be populating back over to the "Results" page and looping through to the next.