Macro Help

[Jariik]

Hello, all.

I'm entirely new to the VBA language, and currently in a situation where I don't necessarily have time to learn it (Overseas with USMIL).

I have a spreadsheet which conducts several calculations across multiple sheets, ultimately boiling down the results to a range of 4 cells. I'm attempting to stress-test it (RANDBETWEEN is involved), and it do so I'm trying to get the spreadsheet to automatically recalculate, copy from a small range of cells (AS9:AV9), and paste the values in a table (AZ11:BC110). I know certain aspects (paste special: values, F9 to recalculate), but ultimately I'm entirely lost on VBA macros and don't really have time to learn from the ground up.

Would anybody be able to help? It's not particularly pressing, but it'd make life a lot easier.

Thank you in advance.

 

[MARK858]

the values in a table (AZ11:BC110). I know certain aspects (paste special: values, F9 to recalculate), but ultimately I'm entirely lost on VBA macros and don't really have time to learn from the ground up.

You don't need to you can do everything you have asked for with the macro recorder in minutes without anyone writing code for you.

First if you can't see it make the developer tab visible
view developer tab in excel 2013

Click the developer tab then click the record button.
When the dialog box opens where it says "Store macro in" click the dropdown and select "Personal Macro Workbook".
Instead of clicking F9 click the formula tab then click Calculate.
Carry out your copy and paste.
Click Stop recording

Create a button on your QAT and assign your macro

Insert macro button in the Quick Access Toolbar on Ribbon

 

[Jariik]

Thank you. I was able to use your suggestion as a starting point and came up with the following:

Sub calc3()
'
' calc3 Macro
'
'
Range("AZ10:BC10").End(xlUp).Offset(1).Select
Do
Calculate
Range("AS9:AV9").Copy
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Loop Until ActiveCell.Row = 110
End Sub

I'm not sure if it's the most efficient manner, but it works. Thank you for your help!

 

[MARK858]

I don't know exactly what you are trying to achieve with your end(xlup) line but for the rest the below will probably be a bit better (although untested so test on a copy).

Sub calc3b()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 11 To 110
        Calculate
        Range("AS9:AV9").Copy
        Range("AZ" & i).PasteSpecial Paste:=xlPasteValues
    Next
    Application.ScreenUpdating = True
End Sub

or

Sub calc3c()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 11 To 110
        Calculate
        With Range("AS9:AV9")
        Range("AZ" & i).Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    Next
    Application.ScreenUpdating = True
End Sub