Can these 2 simple macro be written better?

[Mathman]

Looking to see better variation for running these macros.

If you can help that would be great!

1-

Sub Calculation()



shtEarnings.Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "=RC[7]/RC[9]-1"
Selection.AutoFill Destination:=Range("H3:H52")


End Sub




2-


Sub Max ()


shtEarnings.Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=MAX(<wbr>MostRecentQuarterFinancials)"
Range("E1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub

 

[Rick Rothstein]

With the formulae is their a way to get the results only and not the formulae? so I could avoid so many pastes?

Your original code did not show you replacing the formula for your Calculation macro with values... it just left the formula in place, so that is what I (and others) gave you in our simplification. To do what you are now asking for means I need to know exactly what shtEarnings is... is it a sheet code name or a variable? If a variable, show us the code line assigning its value to it.

 

[Mathman]

Hi Rick

Thank you very much for your help, it's much appreciated.

I've ran out of time tonight but I will be back at it in the morning. I will do as you mentioned and let you know the results. I'm trying to wrap my head around leaning this stuff so I will do it to my best then let you have a look at it. The paste I have set up at the end of the code, I copy the sheet then paste values only. This was how I managed to get values only. I know, it's not pretty but I had to start somewhere.

Thanks again, you're a great help!

MM

 

[Mathman]

So this is what I have so far. It works but moves like a turtle. This is 1 half of the macro, the other is identical but uses shtBalanceSheet. I have no dim, I really don't know what could make it faster and although I have no idea how to shorten it the paste for each seems repetitive. Also the last line of code is my way to get the formulae and turn them into values only. If you see a way to shorten this and make it run faster please do let me know.

Thanks again!




Sub QTR_Update()
'
' EarningsUpdate Macro
'
Application.ScreenUpdating = False
Application.EnableEvents = False
EnableConditionalFormats False









With shtEarnings


shtBuyDashboard.Range("<wbr>TickerSource").Copy
.Range("<wbr>EarningsDashboardTicker").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("E1").Value = [MAX(<wbr>MostRecentQuarterFinancials)]
shtBuyDashboard.Range("<wbr>MostRecentQuarterFinancials").<wbr>Copy
.Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>RevenuesPerShare").Copy
.Range("O3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>RevenuesPerShareHQ").Copy
.Range("K3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>RevenuesPerShareLQ").Copy
.Range("M3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>CostPerShare").Copy
.Range("AD3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>CostPerShareHQ").Copy
.Range("Z3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>CostPerShareLQ").Copy
.Range("AB3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>NetIncomePerShareMinusLow").<wbr>Copy
.Range("AK3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>NetIncomePerShareLow").Copy
.Range("AQ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>NetIncomePerShareHQ").Copy
.Range("AP3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>NetIncomePerShareLQ").Copy
.Range("AR3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

.Range("G3:G52").FormulaR1C1 = "=RC[8]/RC[9]-1"
.Range("H3:H52").FormulaR1C1 = "=RC[7]/RC[9]-1"
.Range("I3:I52").FormulaR1C1 = "=RC[6]/RC[9]-1"
.Range("J3:J52").FormulaR1C1 = "=RC[5]/RC[9]-1"
.Range("L3:L52").FormulaR1C1 = "=RC[3]/RC[8]-1"
.Range("V3:V52").FormulaR1C1 = "=RC[8]/RC[9]-1"
.Range("W3:W52").FormulaR1C1 = "=RC[7]/RC[9]-1"
.Range("X3:X52").FormulaR1C1 = "=RC[6]/RC[9]-1"
.Range("Y3:Y52").FormulaR1C1 = "=RC[5]/RC[9]-1"
.Range("AA3:AA52").FormulaR1C1 = "=RC[3]/RC[8]-1"

'Copy and paste so formulaes turn into values only

.Range("B1:AR52").Copy
.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



EnableConditionalFormats True
Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Save

End Sub




End With

 

[Rick Rothstein]

That is not your actual code, or at least not all of it, as you have your End With statement following your End Sub statement. If I am going to help you speed up your code, I need to see all of the code involved with your QTR_Update macro. For example, are you calling an outside subroutine named EnableConditionalFormats? More importantly, I need to know exactly what shtEarnings and shtBuyDashboard are? You are using them like they were variables OR like you physically renamed the Code Name for your worksheets... I need to know which. If they are variables, then I need to know what values were assigned to them. I would also like to know the ranges associated with all of those Defined Names that you have used. Is there anyway you can post your entire Workbook for us to see? It would be so much easier if I could look for all the things I need to see directly as opposed to trying to direct you to get them for me piecemeal.

 

[Mathman]

Hi Rick

The end is a mistake when I copied and pasted the code for this post, should be End With followed by End Sub which is what I currently have.

I am calling an outside routine named EnableConditionsFormat but this doesn't need to be used, I was trying to build from what I already had in my workbook.

The shtEarnings and shtBuyDashboard are the sheets within the workbook which I have renamed under the Excel Objects, I couldn't tell you based on my knowledge if they are variables......

The workbook is rather large and I just don't see how I could post it. I can if you want list the cells for all the named ranges for you if you wanted? Would that help?



Again excuse my lack of knowledge here. I need some time to catch up.

 

[Rick Rothstein]

The shtEarnings and shtBuyDashboard are the sheets within the workbook which I have renamed under the Excel Objects, I couldn't tell you based on my knowledge if they are variables......

You selected the worksheet in the Project-VBAProject window and then changed its Name property in the Properties window... that was the Code Name for the sheet that you changed. And that is what I wanted to know for shtEarnings and shtBuyDashboard that you used in your code.

I can if you want list the cells for all the named ranges for you if you wanted? Would that help?

Yes, more than likely it would help. It might be easier to design faster code using the actual ranges as a group (letting the code figure out where the last rows are to keep it dynamic) rather than having to reference each individual named range depending, of course, on how aligned each range is to each other.

 

[Mathman]

When I run my model I have some data pulled and copied to a section that I use for this particular macro. The reason I have used so many named ranges was due to the data table it is pulled from can in time expand and contract. The columns have remained the same but it's the rows that could expand/contract. Would this cause an issues with how you are currently thinking I can pull the data with not using named ranges?

Not sure if this shot will work.

 

[Rick Rothstein]

When I run my model I have some data pulled and copied to a section that I use for this particular macro. The reason I have used so many named ranges was due to the data table it is pulled from can in time expand and contract. The columns have remained the same but it's the rows that could expand/contract. Would this cause an issues with how you are currently thinking I can pull the data with not using named ranges?

Not sure if this shot will work.

Your picture did not come through. As for the named ranges, as long as I know the sheet name, column and starting cell for the named range, I can have VB code figure out where the ending cell is at the time the code runs. It may be that your data is so scattered about that I can't do anything faster with them, but I won't know that until I see how each Defined Name is defined.

 

[Mathman]

Here is 100% of the macro as I run it. I have commented the range area for all defined names as 1- define name 2- Excel Objects sheet name I have assigned to it and 3- were the name range references to.

I hope this is what you needed, if anything is missing or you have a question just let me know.

Thanks again.




Sub QTR_Update()
'
' EarningsUpdate Macro
'
Application.ScreenUpdating = False
Application.EnableEvents = False
'EnableConditionalFormats False



'SuperCycle Sheet

With shtSuperCycle

'MostRecentQuarterFinancials =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$94:$EU$94
.Range("D3").Value = [MAX(<wbr>MostRecentQuarterFinancials)]
.Range("D2").Value = [MIN(<wbr>MostRecentQuarterFinancials)]
shtBuyDashboard.Range("<wbr>MostRecentQuarterFinancials").<wbr>Copy
.Range("F5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End With



With shtEarnings

' ("TickerSource")=(<wbr>shtBuyDashboard)='A7_Buy Dashdoard'!$EX$2:$EX$51
shtBuyDashboard.Range("<wbr>TickerSource").Copy
.Range("<wbr>EarningsDashboardTicker").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'MostRecentQuarterFinancials =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$94:$EU$94
.Range("E1").Value = [MAX(<wbr>MostRecentQuarterFinancials)]
shtBuyDashboard.Range("<wbr>MostRecentQuarterFinancials").<wbr>Copy
.Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("RevenuesPerShare")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$38:$EU$43
shtBuyDashboard.Range("<wbr>RevenuesPerShare").Copy
.Range("O3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("RevenuesPerShareHQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$44:$EU$44
shtBuyDashboard.Range("<wbr>RevenuesPerShareHQ").Copy
.Range("K3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("RevenuesPerShareLQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$45:$EU$45
shtBuyDashboard.Range("<wbr>RevenuesPerShareLQ").Copy
.Range("M3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("CostPerShare")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$46:$EU$51
shtBuyDashboard.Range("<wbr>CostPerShare").Copy
.Range("AD3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("CostPerShareHQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$52:$EU$52
shtBuyDashboard.Range("<wbr>CostPerShareHQ").Copy
.Range("Z3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("CostPerShareLQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$53:$EU$53
shtBuyDashboard.Range("<wbr>CostPerShareLQ").Copy
.Range("AB3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("NetIncomePerShareMinusLow")<wbr>= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$54:$EU$58
shtBuyDashboard.Range("<wbr>NetIncomePerShareMinusLow").<wbr>Copy
.Range("AK3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("NetIncomePerShareLow")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$59:$EU$59
shtBuyDashboard.Range("<wbr>NetIncomePerShareLow").Copy
.Range("AQ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("NetIncomePerShareHQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$60:$EU$60
shtBuyDashboard.Range("<wbr>NetIncomePerShareHQ").Copy
.Range("AP3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'("NetIncomePerShareLQ")= (shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$61:$EU$61
shtBuyDashboard.Range("<wbr>NetIncomePerShareLQ").Copy
.Range("AR3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

.Range("G3:G52").FormulaR1C1 = "=RC[8]/RC[9]-1"
.Range("H3:H52").FormulaR1C1 = "=RC[7]/RC[9]-1"
.Range("I3:I52").FormulaR1C1 = "=RC[6]/RC[9]-1"
.Range("J3:J52").FormulaR1C1 = "=RC[5]/RC[9]-1"
.Range("L3:L52").FormulaR1C1 = "=RC[3]/RC[8]-1"
.Range("V3:V52").FormulaR1C1 = "=RC[8]/RC[9]-1"
.Range("W3:W52").FormulaR1C1 = "=RC[7]/RC[9]-1"
.Range("X3:X52").FormulaR1C1 = "=RC[6]/RC[9]-1"
.Range("Y3:Y52").FormulaR1C1 = "=RC[5]/RC[9]-1"
.Range("AA3:AA52").FormulaR1C1 = "=RC[3]/RC[8]-1"

'Copy and paste so frmulaes turn into values only

.Range("B1:AR52").Copy
.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End With





With shtBalanceSheet

' ("TickerSource")=(<wbr>shtBuyDashboard)='A7_Buy Dashdoard'!$EX$2:$EX$51
shtBuyDashboard.Range("<wbr>TickerSource").Copy
.Range("<wbr>BalanceSheetDashdoardTicker").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'MostRecentQuarterFinancials =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$94:$EU$94
.Range("E1").Value = [MAX(<wbr>MostRecentQuarterFinancials)]
shtBuyDashboard.Range("<wbr>MostRecentQuarterFinancials").<wbr>Copy
.Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'BalanceSheetBookValues =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$62:$EU$69
shtBuyDashboard.Range("<wbr>BalanceSheetBookValues").Copy

'BalanceSheetBookValuesInput =(shtBalanceSheet) ='A0_Balance Sheet Dashdoard'!$BA$3
.Range("<wbr>BalanceSheetBookValuesInput").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>BalanceSheetQuickRatios").Copy

'BalanceSheetQuickRatiosInput =(shtBalanceSheet) ='A0_Balance Sheet Dashdoard'!$BJ$3
.Range("<wbr>BalanceSheetQuickRatiosInput")<wbr>.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
shtBuyDashboard.Range("<wbr>BalanceSheetCashValues").Copy

'BalanceSheetQuickRatiosInput =(shtBalanceSheet) ='A0_Balance Sheet Dashdoard'!$BJ$3
.Range("<wbr>BalanceSheetCashValuesInput").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'BalanceSheetDebtValues =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CX$86:$EU$93
shtBuyDashboard.Range("<wbr>BalanceSheetDebtValues").Copy
.Range("<wbr>BalanceSheetDebtValuesInput").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

'CurrentStockPrices =(shtBuyDashboard) ='A7_Buy Dashdoard'!$CO$3:$CO$52
shtBuyDashboard.Range("<wbr>CurrentStockPrices").Copy
.Range("<wbr>CurrentStockPricesInput").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Range("BA3:BA52").Copy
.Range("G3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BG3:BG52").Copy
.Range("M3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BH3:BH52").Copy
.Range("O3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BJ3:BJ52").Copy
.Range("Q3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BP3:BP52").Copy
.Range("V3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BQ3:BQ52").Copy
.Range("X3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BS3:BS52").Copy
.Range("Z3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BY3:BY52").Copy
.Range("AF3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("BZ3:BZ52").Copy
.Range("AH3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CB3:CB52").Copy
.Range("AJ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CH3:CH52").Copy
.Range("AP3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CI3:CI52").Copy
.Range("AR3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CC3:CC52").Copy
.Range("AL3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CD3:CD52").Copy
.Range("AM3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CE3:CE52").Copy
.Range("AN3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CF3:CF52").Copy
.Range("AO3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Range("CG3:CG52").Copy
.Range("AQ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

.Range("H3:H52").FormulaR1C1 = "=RC[-7]/RC[-1]"
.Range("I3:I52").FormulaR1C1 = "=RC[44]/RC[45]-1"
.Range("J3:J52").FormulaR1C1 = "=RC[43]/RC[45]-1"
.Range("K3:K52").FormulaR1C1 = "=RC[42]/RC[45]-1"
.Range("L3:L52").FormulaR1C1 = "=RC[41]/RC[45]-1"
.Range("N3:N52").FormulaR1C1 = "=RC[39]/RC[44]-1"
.Range("R3:R52").FormulaR1C1 = "=RC[44]/RC[45]-1"
.Range("S3:S52").FormulaR1C1 = "=RC[43]/RC[45]-1"
.Range("T3:T52").FormulaR1C1 = "=RC[42]/RC[45]-1"
.Range("U3:U52").FormulaR1C1 = "=RC[41]/RC[45]-1"
.Range("W3:W52").FormulaR1C1 = "=RC[39]/RC[44]-1"
.Range("AA3:AA52").FormulaR1C1 = "=RC[-1]/RC[-26]"
.Range("AB3:AB52").FormulaR1C1 = "=RC[43]/RC[44]-1"
.Range("AC3:AC52").FormulaR1C1 = "=RC[42]/RC[44]-1"
.Range("AD3:AD52").FormulaR1C1 = "=RC[41]/RC[44]-1"
.Range("AE3:AE52").FormulaR1C1 = "=RC[40]/RC[44]-1"
.Range("AG3:AG52").FormulaR1C1 = "=RC[38]/RC[43]-1"
.Range("AK3:AK52").FormulaR1C1 = "=RC[-1]/RC[-36]"


'Copy and paste so frmulaes turn into values only

'CurrentStockPrices =(shtBalanceSheet) ='A0_Balance Sheet Dashdoard'!$A$1:$AR$52
.Range("BalanceSheetAll").Copy
.Range("BalanceSheetAll").<wbr>PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



End With



'EnableConditionalFormats True
Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Save

End Sub