Simple VBA Copy & Paste Loop

[Stuaart]

Hi there MrExcel community, this is my first forum post! So I have just recently started a new job and blagged that I could do some basic VBA and ...well... let's just say that I am on a steep learning curve

I have a basic repetitive task that I am hoping one of you could help me with. I've spent around 30 mins trying it, however, I am still getting to grips with the basic VBA language, operators, expressions and so on. What I am trying to do is this:


On sheet "sensitivity analysis" select Cell A9 and paste the value into A3
Select Cells I3:K3 and paste into Cells I9:K9

Then repeat for line 10 as follows:=

On sheet "sensitivity analysis" select Cell A10 and paste the value into A3
Select Cells I3:K3 and paste into Cells I10:K10

The repeat for lines 11 onward until the cell is blank.

This is effectively taking a scenario ID in column A and entering it into cell A3. This triggers cells B2 to D2 to lookup the values from the appropriate row which serve as inputs for a scenario in a model. The outputs of the scenario are calculated (from other sheets so Data Table cannot be used) into cells I:K.

I'd like to run 1,000 scenarios.

I hope this makes sense, any questions please let me know. Your help is much appreciated.

 

[cooper645]

Hi Stuart,

Remember to try macros on a copy of your workbook first.
Please give the following a go. I believe it is what you are after.

code Error, revisiting now

 

[cooper645]

Apologies,

have now got the code working

Option Explicit

Sub SaveStuart()
Dim i As Long


On Error GoTo ErrHandle
Application.ScreenUpdating = False


Sheets("sensitivity analysis").Select
For i = 9 To 1009
Cells(3, 1).Value = Cells(i, 1).Value
Range(Cells(i, 9), Cells(i, 11)).Value = Range(Cells(3, 9), Cells(3, 11)).Value
Next i


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Exit Sub


ErrHandle:
Application.ScreenUpdating = True
MsgBox "The code has encountered an error", vbOKOnly + vbCritical, "Error"
Exit Sub


End Sub

 

[hiker95]

Stuaart,

Welcome to the MrExcel forum.

I am still getting to grips with the basic VBA language, operators, expressions and so on.

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 2/3/2018

https://www.mrexcel.com/forum/excel...ere-entries-another-column-2.html#post5001046

 

[kvsrinivasamurthy]

Try this code

Sub Macro3()Dim LR As Long


With Sheets("sensitivity analysis")
LR = Range("A9").End(xlDown).Row
Range("A3") = Range("A" & LR)
Range("I" & LR & ":K" & LR) = Range("I3:K3")
End With


End Sub

 

[Stuaart]

Hi cooper645 just tried this code and it works effectively, perfect

Thank you for taking the time to write this.

 

[Stuaart]

Hi hiker95, thanks for the suggestion, this is exactly the sort of content I need to be ready right now.

 

[hiker95]

Hi hiker95, thanks for the suggestion, this is exactly the sort of content I need to be ready right now.

Stuaart,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.