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

[Murthy]

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.

 

[Dave Miller]

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

 

[Murthy]

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]

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






- 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.