Macro Help ... Pass values to the excel based model and copy + pas
 

Hello all:

I have a big excel model which does some analysis.

I have to run the model on approx 150 different values and analyze the
results.

I need help to automate this process.

A macro that can:

1. Pass the values to the model (in Sheet1, cell A1 - from Sheet2, cells
A1:A150 - one at a time)
2. Wait for 10 seconds (to finish calculations)
3. Paste the values (values located on Sheet 1, A2: A10 into Sheet 3)

Thank you.

Macro Help ... Pass values to the excel based model and copy +pas
 

On Jan 15, 4:36*pm, Murthy wrote:
Hello all:

I have a big excel model which does some analysis.

I have to run the model on approx 150 different values and analyze the
results.

I need help to automate this process.

A macro that can:

1. Pass the values to the model (in Sheet1, cell A1 - from Sheet2, cells
A1:A150 - one at a time)
2. Wait for 10 seconds (to finish calculations)
3. Paste the values (values located on Sheet 1, A2: A10 into Sheet 3)

Thank you.

Sub RunModel()
Dim r, c as Range
Set r = Sheets("Sheet2").range("A1:A150")
For Each c In r
With Sheets("Sheet1")
.Activate
.Range("A1").Value = c.value
.Calculate
.Range("A2:A10").Copy
End With
With Sheets("Sheet3")
.Activate
.Range("A" & .Range("A65536").End(xlUp).Row +
1).PasteSpecial
End With
Next
End Sub

Macro Help ... Pass values to the excel based model and copy +
 

Thanks Dave. Really helpful.

When I try to compile, I get a compile error at

..Range("A" & .Range("A65536").End(xlUp).Row + 1).PasteSpecial

I am a newbie to this programming. Sorry.

Regards,
Murthy

"Dave Miller" wrote:

On Jan 15, 4:36 pm, Murthy wrote:
Hello all:

I have a big excel model which does some analysis.

I have to run the model on approx 150 different values and analyze the
results.

I need help to automate this process.

A macro that can:

1. Pass the values to the model (in Sheet1, cell A1 - from Sheet2, cells
A1:A150 - one at a time)
2. Wait for 10 seconds (to finish calculations)
3. Paste the values (values located on Sheet 1, A2: A10 into Sheet 3)

Thank you.

Sub RunModel()
Dim r, c as Range
Set r = Sheets("Sheet2").range("A1:A150")
For Each c In r
With Sheets("Sheet1")
.Activate
.Range("A1").Value = c.value
.Calculate
.Range("A2:A10").Copy
End With
With Sheets("Sheet3")
.Activate
.Range("A" & .Range("A65536").End(xlUp).Row +
1).PasteSpecial
End With
Next
End Sub

Macro Help ... Pass values to the excel based model and copy +
 

On Jan 15, 5:53*pm, Murthy wrote:
Thanks Dave. Really helpful.

When I try to compile, I get a compile error at *

.Range("A" & .Range("A65536").End(xlUp).Row + 1).PasteSpecial

I am a newbie to this programming. Sorry.

Regards,
Murthy



"Dave Miller" wrote:
On Jan 15, 4:36 pm, Murthy wrote:
Hello all:

I have a big excel model which does some analysis.

I have to run the model on approx 150 different values and analyze the
results.

I need help to automate this process.

A macro that can:

1. Pass the values to the model (in Sheet1, cell A1 - from Sheet2, cells
A1:A150 - one at a time)
2. Wait for 10 seconds (to finish calculations)
3. Paste the values (values located on Sheet 1, A2: A10 into Sheet 3)

Thank you.

Sub RunModel()
* * Dim r, c as Range
* * Set r = Sheets("Sheet2").range("A1:A150")
* * For Each c In r
* * * * With Sheets("Sheet1")
* * * * * * .Activate
* * * * * * .Range("A1").Value = c.value
* * * * * * .Calculate
* * * * * * .Range("A2:A10").Copy
* * * * End With
* * * * With Sheets("Sheet3")
* * * * * * .Activate
* * * * * * .Range("A" & .Range("A65536").End(xlUp).Row +
1).PasteSpecial
* * * * End With
* * Next
End Sub- Hide quoted text -

- Show quoted text -

Make sure that you have pasted the code into ThisWorkbook section of
code or a new Module.

If that line ends with the + sign then, go to the end of the line and
press delete to bring the rest of the code up to that line.