Copy cell values (not formulas) from a pair of rows then paste in a new set of rows

[pelle3988]

Hi,
I am new to this forum even though I have been a frequent visitor over the past couple of months. I am a mechanical engineering student and only have experience in excel and programming in VBA from work. I am tasked to produce a cash flow report that needs to be fluid in the sense that payment percentages need to be able to be moved to different months (columns) in the same row it is being pasted in by changing the lead time. Also if someone could help me figure out how to get the last payment percentage to match up with the month on the "Supplier Input" tab that would be greatly appreciated as well.

Thanks,
Nate

[pelle3988]

*Bump No Response*

[AB33]

Pelle, the legend Santos player
You have put in the code on worksheet event, but you do not have any triggering target in your code.
You should put the code on the standard module unless you want an event driven code.
Try this code on standard module.

Please Login or Register  to view this content.

[pelle3988]

Attachment 252323AB33,

Thank you for the reply and the code you have provided. Everything runs great except that I have to hit F5 in VBA to make the rows switch in excel, when it should switch once Standard or Custom is chosen from the drop down menu. Also if you could help me in module 1 in the Excel file that I am uploading nowVBA Help Revised.xlsm that would be greatly appreciated as well. I have made comments in the code itself to help explain what I am trying to do and would like it to do. Hopefully you can continue to help me learn.

Thanks,
Nate

[AB33]

Nate,
Not sure I understand your request, but if you want to run the code from a button you have in H1, you need to right click on the button, then choose assign macro, need to find the name of the macro(This workbook, then choose the name). You can then press the button to run the code.

[pelle3988]

AB33,

What I mean is, I need code that will calculate the difference between the month number I am trying to lookup using the index function and then subtract the value in cell C2.

Please Login or Register  to view this content.

After that I am trying to copy the values the suppliers are generating and then select the cell G7 and use an offset function to move it H7 or I7 dependent upon what "x" equals and the addition of the value under Net Terms cell (D2)

Please Login or Register  to view this content.

I hope I made it more clear what I am trying to do and you are able to help.
Thanks,
Nate

[AB33]

Please Login or Register  to view this content.

is wrong.
Index returns(Array, row number, optional column number).
It also returns a variant, not a long, so if you change the

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

But you still get an error.
I personally do not like the match and index worksheet functions to use them in a code, there are other options.