VBA: Running Solver on Multiple Data Sets, Copying Output to Indexed Columns

[SconnorA]

An outline of my code is as follows;
1. Copy single column of data from raw data sheets, of which there are 5 in a separate workbook, to the curve fitting sheet (different workbook).
2. Run solver on these data to minimize the sum of the squares.
3. Copy the output to the i'th row of column A/B/C/D/E (A for first raw data fro sheet1, B for second etc.)
4. Repeat on the next column of raw data from the first sheet (do this until all data processed through Solver)
5. Move to the next sheet and repeat for all raw data.

I am having a few issues with the copying of raw data to the curve fitting sheet as well as the output to the indexed columns. As far as I know the Solver is running, yet on the first run through the macro there was no data showing in the required column and the output copying failed. Any help is greatly appreciated, here is the code for reference:


Option Explicit


Sub CurveFit()
Dim i As Single
Dim Output() As Single 'Define this dynamic array for the solver output


'Process the DMSO data
For i = 1 To 45 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "A").PasteSpecial xlPasteValues <---GET AN ERROR HERE
Next i


i = 0


' Process the NaCl Data
For i = 1 To 102 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate

Sheet6.Cells(i, "B").PasteSpecial xlPasteValues
Next i


i = 0


' Process the Sucrose data
For i = 1 To 72 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "C").PasteSpecial xlPasteValues
Next i

i = 0


' Process the Et Glycol Data
For i = 1 To 65 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "D").PasteSpecial xlPasteValues


Next i

i = 0


' Process the Glycerol Data
For i = 1 To 47 Step 1
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet1.Columns(i).Copy
Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate
Sheet1.Range("C10").PasteSpecial xlPasteValues
SolverReset
SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:=Range("$B$2:$B$4")
SolverSolve
Sheet1.Range("B4").Copy
Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Activate
Sheet6.Cells(i, "E").PasteSpecial xlPasteValues
Next i


End Sub

 

[SconnorA]

So I have activated the sheet prior to running Solver to be ensure it is active. However, I still get the same problem with it not running while the copy/paste continues.

        Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate        
        SolverReset
        SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:="$B$2:$B$4"
        SolverSolve True
        Sheet1.Range("B4").Copy
        Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "A").PasteSpecial xlPasteValues

 

[AlphaFrog]

So I have activated the sheet prior to running Solver to be ensure it is active. However, I still get the same problem with it not running while the copy/paste continues.

        Workbooks("Fresnel Curve Fitting - Macro Enabled.xlsm").Activate        
        SolverReset
        SolverOk SetCell:="$E$7", MaxMinVal:=2, ByChange:="$B$2:$B$4"
        SolverSolve True
        Sheet1.Range("B4").Copy
        Workbooks("Calibration Range 1 Normalized Profiles.xlsx").Sheets(6).Cells(i, "A").PasteSpecial xlPasteValues

I'm out of ideas. Sorry.
If you want to upload an example workbook to a file share site, I'll have a look.

 

[SconnorA]

You helped me out tremendously, I commend your efforts. Enjoy your day

 

[SconnorA]

I found the issue. In Excel 2011 for Mac, Solver is an external program that does not run while the VBA is open. Thus, the problem was not my code yet the program. Again, thank you.

 

[AlphaFrog]

I found the issue. In Excel 2011 for Mac, Solver is an external program that does not run while the VBA is open. Thus, the problem was not my code yet the program. Again, thank you.

You're welcome. For future reference, state your OS and Excel version.