Trying to automate with a macro adding data to my solver model and triggering solver

Hi All,

Basically I like building basic solver models and have attached a section of one in which, I have tried to model ratings for the Rugby Super League - somewhat unsuccessfully (or maybe this kind of modelling doesn't work for that). In short, I add data to feed the model (so to speak), make a few changes, run solver, get some new ratings etc. Basically I am looking to try and automate this process somewhat.

In the attached (I have tried to highlight everything involved red to help), I have taken rows 143-150 on sheet 1 (where I added last weeks results in), copied down my text functions in columns, L and M and S and T, and then pasted the values into columns C, D, E and F - from row 185 down on sheet 3. I have also copied the date across and re-formatted it from sheet 1, to column A on sheet 3. I have then copied down on sheet 3, columns, B and H to N from row 184 through 192.

I have then tweaked three values in row 46, D2, F2 and F3 and I1.

I have then copied the two blocks of 1's, into the cells I want Solver to calculate for me (as I just find that I seem to get cleaner values starting from scratch everytime as it doesn't always converge properly or takes forever and it seems quicker and cleaner to solve from effectively a constant rating everytime).

I then ran Solver.

This gave me a Macro of (the Solver element doesn't now seem to work properly - apologies I recorded this as I am not fluent in Macro and really don't know why )

Sub Solving()
'
' Solving Macro
'

'
    Range("L142:M142").Select
    Selection.Copy
    Range("L143:M150").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("N142:V142").Select
    Selection.Copy
    Range("N143:V150").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A143").Select
    Sheets("Sheet3").Select
    ActiveWindow.ScrollRow = 163
    Sheets("Sheet1").Select
    Range("L143:M150").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("C185").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Sheets("Sheet1").Select
    Range("S143:T150").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("E185").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Sheets("Sheet1").Select
    Range("A143:A150").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A185").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "dd/mm/yyyy"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("B184").Select
    Selection.Copy
    Range("B185:B192").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G184:N184").Select
    Selection.Copy
    Range("G185:N192").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A185").Select
    ActiveWindow.ScrollRow = 1
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "25"
    Range("D3").Select
    ActiveWindow.ScrollRow = 33
    Range("E46").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[2]C:R[146]C)"
    Range("F46").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[2]C:R[146]C)"
    Range("F47").Select
    ActiveCell.FormulaR1C1 = "]"
    Range("I46").Select
    ActiveCell.FormulaR1C1 = "=STDEV(R[2]C:R[146]C)"
    Range("A47").Select
    ActiveWindow.ScrollRow = 1
    Range("D1").Select
    Selection.Copy
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(R[46]C[4]:R[190]C[4])"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[47]C[5]:R[191]C[5])"
    Range("I26:K39").Select
    Selection.Copy
    Range("I10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("Q26:R39").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWindow.ScrollColumn = 1
    SolverOk SetCell:="$I$1", MaxMinVal:=2, ValueOf:=0, ByChange:= _
        "$F$3,$I$10:$K$23,$Q$10:$R$23", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverSolve
End Sub

This has given me a couple of thoughts:

Is actually possible to trigger Solver from a Macro?
Can I change the code so that the Macro will automatically figure, which cells to copy and paste from on sheet 1 and where to paste them on sheet 3? (I'm figuring I would need to do some irow business and pick an appropriate cell before running the Macro?)
Similarly the cells I'm changing on sheet 3 (particularly F2 which isn't critical but I1 is), have to account for the last row of data added (e.g. 192 in this case) - is this possible?
D2 (on sheet 3) has to be one more than the highest value in the last row of column B (e.g. D2 is 25 in this case as column B has a high vaklue of 24) - is this possible (I'm not overly worried about it for this particular model but would like to use this in something else I do)?
Can I make what I have done above tidier?

I think this covers everything that I was trying to do. Apologies for the slightly basic nature of this, I'm not particularly fluent when it comes to Macro's and was just wondering if I could make this process easier. Ultimately I'd like to put web queries into this so that I could make the process down to just a couple of clicks (but that can wait).

Thanks in advance for any suggestions and advice.

Rugby League_Super_League_2014_Unweighted_RevE_EFCopy_Test.xlsm