Ugly VBA

V

VBAMePlease

Board Regular
Joined
Jun 19, 2017
Messages
59
Code: 
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Some better context:

I would like to have the VBA prompt the user with a Yes/No Message box - if "Yes", then Run, if "No" then end sub.
If the script is executed upon a "Yes" response it needs to go to "Dashboard" sheet, copy the value in D4 and paste the value on "Rate Analysis" sheet in L40.
After this, the script needs to go to the "Results" sheet, copy the value in C16, paste to "Rate Analysis" L26, go back to "Results", copy the value in D16, paste to "Rate Analysis" L27. Then it needs to run the "Run_BR_Query" macro. Then it needs to copy the results on "Rate Analysis" P28:AN28 and paste this strip of values in E16 on the "Results" sheet. After this is completed, it needs to run the "Clear_Results" macro.
From here on, the script needs to repeat the italiscized portion above, but copy and paste C17 and D17 on the "Results" sheet, and copy the results ("Rate Analysis" P28:AN28) to cell E17 on the "Results" sheet.
In this instance, it would have to perform this from C,D,E16 all the way to C,D,E125.
Rather than repeating the macro in the code text above 120 times, is there any way to define a range from a start point (C16,D16) and complete the "Run_BR_Query" macro for all values beneath that until it hits a blank cell?
 
Upvote 0
I and many others are at work. You have a lot going on here so please be patient. Let's try and fix a few things. Hopefully you can figure out the pattern of the components I've cleaned up and can extend to the rest. Copy and paste is icky. You can assign the value of a Sheet("insert")range("insert).value to the value that is from another Sheet("insert")range("insert").value



Code: 
[COLOR=#222222][FONT="Verdana"]Private Sub CommandButton2_Click() [/FONT][/COLOR]

With Worksheets("Rate Analysis")
          .Range("L40").Value = Sheets(“Dashboard").Range("D4").Value
          .Range("L26").Value = Sheets("Results").Range("C16").Value
          .Range("L27").Value = Sheets("Results").Range("D16").Value
End With

Call Macro Run_BR_Query - I'm Presuming that your P28 cell is located in the "Rate Analysis" Sheet.

Code: 
Dim lCol As Long
lCol = Cells(28, Columns.Count).End(xlToLeft).Column  'Identifies how many columns from Row 28 have data.
 
Sheets("Results").Range("E16:E" Cells(16, lCol - 11).Address &).Value = Sheets("Rate Analysis").Range("P28:P" & Cells(28, lCol).Address).Value    ’11 is subtracted as to count from Column “P” out.

If I have time I'll come back with more. Hopefully others can take over from where I left off. See if you can follow the pattern I have displayed and clean up your copy and paste scripts to do what I have.
 
Last edited:
Upvote 0
Beyond_avarice said:
I and many others are at work. You have a lot going on here so please be patient. Let's try and fix a few things. Hopefully you can figure out the pattern of the components I've cleaned up and can extend to the rest. Copy and paste is icky. You can assign the value of a Sheet("insert")range("insert).value to the value that is from another Sheet("insert")range("insert").value

If I have time I'll come back with more. Hopefully others can take over from where I left off. See if you can follow the pattern I have displayed and clean up your copy and paste scripts to do what I have.
Click to expand...

Yes, the P28 Cell is located on the "Rate Analysis" sheet.

Appreciate the help - no rush on my end here, this is an on-going work project. I have zero experience with loops and am now beginning to realize that the standard record macro protocols aren't going to cut it (i.e. .select etc.)

I'm experimenting with this right now and will post updates lest someone else beats me to the punch. Once again, appreciate the help.
 
Last edited:
Upvote 0
Ugghh I was too hasty The second should read as follows:

Code: 
Dim lCol As Long
lCol = Cells(28, Columns.Count).End(xlToLeft).Column  'Identifies how many columns from Row 28 have data.
 
Sheets("Results").Range("E16:" & Cells(16, lCol - 11).Address ).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value    ’11 is subtracted as to count from Column “P” out.

I should have read all of your code before starting.

1.It appears that you are taking Data starting in Sheets("Results").Range("C16:D16") and moving it to Sheets("Rate Analysis").Range("L26:L27"). This is a transpose.

2. You are running a Macro called "Run_BR_Query". Apparently this only works on Sheets("Rate Analysis").Range("L26:L27"). What does this do?
3 Then Copy the results of Sheets("Rate Analysis").Range("P28:AN28").Value to Sheets("Results").Range("E16").
4. You are then running a macro called "Clear_Results". Apparently this only works on Sheets("Rate Analysis").Range("L26:L27"). What does this do?
5. You want to then move the next row of "Results" data to the "Rate Analysis" sheet and repeat(loop).
6. Does the results in #3 then need to move down one row so that the next iteration will be Sheets("Results").Range("E17")?

7. You indicated a range of C19:D125. Is this a one time script? Will this range change next time to C19:D100 or C19:D300?
 
Upvote 0
Ok I took the liberty of a lot of assumptions. This script is based on your ability to communicate your needs and my imagination(caveat emptor). Because the results of macros cannot be undone; test this on a copy of your workbook. I would recommend walking through the code by going into the Editor and hitting "F8". I am confused as to whether your data starts in C16 or C19 of your "Results" sheet. This script starts with "C16".

Code: 
Sub VBAMePlease()

Dim lCol As Long

Application.ScreenUpdating = False

lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column  'Identifies how many columns from Row 28 have data.

Worksheets("Rate Analysis").Range("L40").Value = Sheets("Dashboards").Range("D4").Value

For i = 16 To 125

'Iterate the two colum values from "Results" sheet to "Rate Analysis" Sheet
    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With

''No idea what this does
Call Run_BR_Query
 
'Populate results and append to Column "E" of "Results" sheet.
'Assuming that the Results are to be appended to Column "E" and iterate down each row of data.
    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value


''Assume this clears out Worksheets("Rate Analysis").Range("L26:L27").Value. If so, not needed
Call Clear_Results

Next i
 
Application.ScreenUpdating = True

End Sub
 
Upvote 0
@Beyond_Avarice

First want to start off by saying thanks for the help. To answer some of your points made in the first post:

1. Yes, the data starts in C16 and D16 in the "Results" sheet (it could go down to C100 and D100), but the paste location will always be L26:L27 in the "Rate Analysis" sheet.

2. The code for Rate BR Query is below:

Code: 
' Run_BR_Query Macro'
' Keyboard Shortcut: Ctrl+Shift+J
'
    Range("P26").Select
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("Q26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("R26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("R30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("S26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("T26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("T30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("U26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("U29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("U30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("V26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("W26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("W29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("W30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("X26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("X29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("X30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("Y26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Y29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Y30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("Z26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Z29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Z30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AA26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AA29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AA30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AB26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AB29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AB30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AC26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AC29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AC30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AD26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AD29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AD30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AE26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AF26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AF29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AF30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AG26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AG29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AG30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AH26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AI26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AI29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AI30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AJ26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AJ29").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AJ30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AK26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AK29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AK30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AL26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AL29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AL30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AM26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AM30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AN26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AN29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AN30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AO26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AO29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AO30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AP26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AP29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AP30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AQ26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AQ29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AQ30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AR26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AR30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AS26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AS29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AS30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AT26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AT29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AT30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AU26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AU29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AU30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AV26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AV29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AV30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AW26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AW29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AW30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AX26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AX29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AX30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AY26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AY29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AY30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("AZ26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AZ29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AZ30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BA26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BA29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BA30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BB26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BB29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BB30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BC26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E28").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BC29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BC29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BC30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BD26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BD29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BD30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BE26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BE29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BE30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BF26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BF29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BF30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BG26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BG29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BG30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BH26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BH29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BH30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BI26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BI29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BI30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BJ26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BJ29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BJ30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BK26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BK29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BK30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BL26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BL29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BL30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("BM26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E27:F27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BM29").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("D30:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BM30").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("P26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

It's a recorded macro that runs an annual production calculator.

3. The results that are generated from running the "Run_BR_Query" Macro is stored on the "Rate Analysis" sheet in cells P28:AN28. This would be copied into E16 ("Results" sheet) when C16:D16 were used, and, as an example, would be copied into E80 when C80:D80 were used as the inputs for the Run_BR_Query macro.

4. The clear results macro clears information in "Rate Analysis". This is unneccessary given that the Run_BR_Query macro can overwrite copied information that feeds P28:AN28.

5. Yes, this should execute a Loop that runs the info in C16:D16 / E16 down to wherever there is the first blank cell. I.e. the script goes to C32:D32, copies the information into E32 and then in C33:D33 if there is a blank cell the script ends the loop.

6. I think I answered this above, but yes, it does.

7. If I could outfit it with the functionality to automatically adjust the range whether it's C16:D30 or C16:D100. If not, I'd be perfectly okay with having to manually adjust the defined range within the script on a situation by situation basis.


I will give your second version a shot today and provide feedback post-trial.

Once again, greatly appreciate the help here.
 
Upvote 0
Testing Updates:

Shortened the length for 'i' from 16 to 125 to 16 to 20 for sake of brevity. In doing so, the script is currently only copying the results from the last iteration (i=20) and copying those results into E16,17,18,19,20. I think this has to do with the fact that we are setting values in E to whatever is in the P28:AN28 range rather than having the macro explicitly copy and paste after each iteration. Or am I incorrect in this logic?

It may be running the Run_BR_Query after each transpose from C16:D16, just not copying the results from the C16:D16 transpose into their respective "E16" slot.

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


If MsgBox("This will take significant memory and may take up to 10 minutes to compute depending upon the number of sub-markets. Ready to run?", vbQuestion + vbYesNo) <> vbYes Then Run


Application.ScreenUpdating = False


lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column


Worksheets("Rate Analysis").Range("L40").Value = Sheets("Dashboard").Range("D4").Value


For i = 16 To 20


    With Worksheets("Rate Analysis")
            .Range("L26").Value = Sheets("Results").Range("C" & i).Value
            .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With
    
Call Run_BR_Query


    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value


Next i


Application.ScreenUpdating = True


End Sub
 
Upvote 0
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"

I'm confused:

Shortened the length for 'i' from 16 to 125 to 16 to 20 for sake of brevity. In doing so, the script is currently only copying the results from the last iteration (i=20) and copying those results into E16,17,18,19,20. I think this has to do with the fact that we are setting values in E to whatever is in the P28:AN28 range rather than having the macro explicitly copy and paste after each iteration. Or am I incorrect in this logic?
Click to expand...

The code takes each iteration from the Sheet("Results").Range("C:D" and i (eg=16)) places it into Sheet("Rate Analysis).Range("L26:L27"), calculations are performed and the results from columns "P:AN"(25 columns are then appended to Sheet("Results").Range("E" and i (eg=16)) and 24 more columns out to the right. The next iteration of (eg i=17) is placed into Sheet("Rate Analysis).Range("L26:L27"), calculated and again the results are placed to Sheet("Results").Range("E" and i (eg=17)) and 24 more columns out to the right.

Are you wanting the results of Sheets("Rate Analysis").Range("P28:AN28").Value to be placed into Column "E" alone as a "Sum", "Average", "Count",etc of all 25 columns?

You indicate that Sheet("Results").Range("E16:E20") all have the same results of the calculation of iteration "20". As stated above. the code takes each iteration of data places the calculated results on the respective row. Unless you have the same data between Sheet("Results").Range("E16:E20"); I would be suspicious of your formulas that are doing the calculation for "P28:AN28" and perhaps how the "Call Run_BR_Query" is placing values that I presume are part of the equation.

I created a test workbook and made my own calculations in Sheets("Rate Analysis").Range("P28:AN28") for data pulled into Sheet("Rate Analysis).Range("L26:L27"). I'm getting results in line with the data for each row in Sheet("Results"). Walk through the script and check that your formulas are calculating correctly. All my script does is populate values between pages. The calculations are in your workbook.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top