Copy/Paste macros

[sush23]

Hi everyone,

I have a workbook with four sheets, " Options Analysis, Sensitivity, Input Data and Output". I need a tool that will

1) Copy every value one by one from the input Sheet (A1:A1000)
2) Paste that Value in the Options analysis sheet Cell P48
3)Copy the Output values from the Sensitivity sheet cells (L13,14,16)
4)Paste those output values in the Output sheet after each other
5) Loop steps 1-4 for the 1000 values of the input sheet,

I am a beginner in Macros and anything VBA, so anybodys help would be much appreciated!!

Thanks
R

 

[macgregorsd]

You should record a macro of your self doing what you need done for the first value in input. Then view the macro code and write a loop around it to do it for all input values.
hope this helps

 

[sush23]

Hi again,

Yes I did that, but i dont know which loop to use/how to write it, and how to make excel paste the output numbers under each other and also keep going down one cell for the input!

Thanks for your help
R

 

[macgregorsd]

Hi everyone,

I have a workbook with four sheets, " Options Analysis, Sensitivity, Input Data and Output". I need a tool that will

1) Copy every value one by one from the input Sheet (A1:A1000)
2) Paste that Value in the Options analysis sheet Cell P48
3)Copy the Output values from the Sensitivity sheet cells (L13,14,16)
4)Paste those output values in the Output sheet after each other
5) Loop steps 1-4 for the 1000 values of the input sheet,

I am a beginner in Macros and anything VBA, so anybodys help would be much appreciated!!

Thanks
R

As far as i see it you do it one by one because you have formulas doing stuff to the input values on options analysis sheet and on sensitivity sheet.
You can write some simple code that will read in the input value, do the formulas and everything you need to calculate inside the code then write the output to the cell you want it to in the output sheet? why do you need to keep copying and pasting?

 

[sush23]

Hi,

Yes, well the forumlas are extremely complicated in this model, so Im trying to avoid writing them in in VBA (they are in the Sensitivity sheet). I need to input each number to the Options Analysis sheet and get the output values from "Sensitivity" . I keep copy and pasting, because I am recording the macro not writing it on VBA, (which is what I want to do) but since this is the first time I am using VBA, I have no idea how to go about it

Thanks
R

 

[macgregorsd]

Ok, well you could try something like this.

Sub_loop()
For i=1 to 1000 *THIS LOOPS THROUGH VALUES
input = sheets("Input Data").cells(i,1).value *SELECTS AND STORES FIRST INPUT VALUE*
sheets("Options Analysis").cells(48,16).value=input *WRITES INPUT VALUE INTO CELL P48*
output1 = Sheets("Sensitivity").cells(13,12).value *STORES VALUE IN CELL L13
output2 = Sheets("Sensitivity").cells(14, 12).value *STORES VALUE IN CELL L14
output3 = Sheets("Sensitivity").cells(16, 12).value *STORES VALUE IN CELL L16

Sheets("Output").Cells(i,1).value=output1
Sheets("Output").cells(i,2).value = output2
Sheets("Output").cells(i,3).value=output3
*THIS PUTS THE THREE VALUE NEXT TO EACHOTHER IN OUTPUT SHEET IN COLUMNS A B AND C. THEN NEXT OUTPUT VALUES WILL GO ON NEXT ROW

Next i

End Sub

Does this make sense?

 

[sush23]

It does make sense. Ill try it right now. Thanks for your help

R

 

[sush23]

I tried the code, it gives me an error (red txt) on the:

Sub_Loop() and when run it says "Compile error, Expected: ="
Input = sheets("Input Data").cells(i,1).value
Sheets("Output Analysis").cells(48,16).value=input


Also, when i press alt F11 to write the code, which sheet do I chose to wrtie the code in? or does it not matter

Thanks so much for your help

 

[macgregorsd]

it doesnt matter which sheet. i out preference would put it in sheet 1

sorry about the errors, its hard to write code straight into this forum when not in excel at same time.

i think first line should be Sub loop() (no underscore)...or instead of loop can call it whatever you like.
instead of input variable call it
inp = Sheets("Input Data").cells(i,1).value
and
Sheets("Output Analysis").cells(48,16).value = inp

 

[sush23]

yeah i figured, I also used Sub x() cause loop wasnt working (could it be cause its a protected word?) Anyway, the program says its running, it has been since your last post, but then it stopped. When i tried it with 5 input numbers it worked. So im thinking its the 1000 numbers that is too much.
Otherwise it works! Do you have any suggestions?

Thanks so so much