copy a number to a precalculated cell and not to the calculation itself

Very much a learner here.

In an Excel spreadsheet I can calculate the location of the next empty cell, which could be anywhere within 300 rows and 10 columns, depending on various situations.

That location is identified in say K4 as being for example G12. I need to copy and paste a number from say B4 to the cell G12.

No matter what I try I finish up going to K4 every time (or getting error messages ! ). How do I tell the operation to go to the nominated cell instead the one that nominated it?

Amazed at promptness of reply. Provided I get this reply setup properly you will see a mock-up simulating the relevant bits of my actual workbook.

The input numbers at the left (B4 etc) change regularly, so I need the next empty cell to be populated as that happens.Book1.xlsm

My first chance to get back to this project. Many thanks for your help so far. However I now have all sorts of problems.

When I revisit your two suggestions, to look at the code, I usually see only the line "option explicit" and nothing else.

If I do somehow manage to see the code I get either a blank Macro dialog box or the message "ambiguous name worksheet_change".

What am I doing wrong? At one stage last week when it all worked (for your first suggestion) I could not get it to work in my little simulated sheet.

What if anything do I need to put in the macro before or after your sub.

I have other questions, but for now would be happy to sort out these problems.

Your patience is much appreciated.

Firstly, In my earlier post I was referring to my doubt whether I would manage to attach a file to my reply, not about setting up your test.

In answer to the questions

1/ Yes, on the first couple of times I looked at your Test BazN and Test BazN 2 they worked. I never got the code to work in a small trial worksheet of my own, and now they don't seem to work as originally sent.

I am sure that I have wrecked something while trying to copy the code to my use.

The Project viewer now seems to be full of mixed up bits of various attempts.

2/ To fill you in, my "real" job is connected with separate but linked workbooks that crunch a large number of engineering measurements and come up with three individual values for any given job. A job may be repeated several times, with new values to be recorded in next available cells in the row for that job. A new job repeats the process in next available row and so on. There are no formulas in the cells used here, they are all copied from the linked worksheet as values.

The attached sheet is a scaled down simulation of the real thing. If we start with apple, the 3 values at upper left are to be copied at first to cells C12, G12, K12. Next time that job is done there will be new values, to be copied to D12, H12, L12.

If a job "pears" is done, that would start in this example at row 13 and the same process starts again.

It must be noted that the jobs could be placed in any row within the sheet, not just always next down from the previous one.

Questions:

Should I disable update external links and save external link values in the worksheet options?

Is the "Worksheet_Changes" in your code a sub or macro name or a necessary piece of code?

Is the "ByVal...." bit just shorthand for something like Dim Range as Value?

Hope this helps you. I really thought I was on a winner with your first reply, but am obviously missing something.

Yes, both Test BazN and Test BazN V2 "work", as downloaded again this morning.

What does happen though is that when I try to look at the codes (using Alt F11 for example) all I see in the code area is "option explicit" and that's it.

I did write down the code for Test BazN , as I saw it in the Project Explorer the first time I downloaded it.

While I saw the code for Test BazN V2 the first time, I never made a note of it.

As I said earlier, I have been unable to get the code used in Test BazN to work in a spreadsheet of my own.

Testing your patience I know, but I feel that we are close to solving my original problem so would like to continue.

Incidentally I most likely don't know how or why the code does its thing, and I don't need to know as long as it works.

Some progress !!

Further to my post of this morning, More of a fluke than anything else, but I found that when I clicked on the down arrow in the General header I got the Worksheet option complete with code. This applies to both the examples you sent.

Furthermore I can get it to work in my practice worksheet, one value at a time, but only if I retain your first line. If I change that to some other sub name etc I get error messages and/or no action.

Is there a simple fix for that, and what should I call a Macro, should it be the same as the sub name?

In order to transfer five values would I need to have extra subs within the sub? I tried just repeating the sub wording after the first lines (before end sub) but that didn't seem to want to work.

Thanks in advance for any help you can give.

Hi again Carim

Have been away from the computer for some time, but want to thank you for your help.

Using the suggestions you gave , plus some experimenting with some ideas I had, my job now works fine.

Having said that, I am sure an expert would find a more compact coding, but I'm happy with the fact that it does what I wanted and I actually have got my excel sheet doing some added fancy stuff that I never planned on doing.