Moving data from sheet1 to sheet2
Posted on 2000-04-19
I have a range of cells (AB1:AB22) on sheet1 that I would like to copy&paste into sheet2.
On sheet2 the target range is on the same row but from Column (D:Z).
Which row on sheet2 the result should end up in depends on the value of "FNR" which is a named cell on sheet1.
The value of "FNR" should be found in column A of sheet2 and the data pasted inte column D of that same row.
I have made macro's doing just this before in Excel4 macro language and I think I would manage to "translate" them into VBA myself but I really don't want to use the Copy&Paste stuff now that I'm getting used to just use "Range(x) = Range(Y).value" instead of "Copy Range(x) then select Range(Y) and paste.
I have named my range (AB1:AB22) "Export" and would like to copy that range (transposed) into column D but not just in any row.
How would I write the code to first determine the right row and then export the range ?
I want this procedure to be done just by pressing a button without me leaving sheet1. It's actually an update of an Excel database we are talking about here. Sheet1 is the form.
What should happen if the code can't find the value of "FNR" in column A of sheet2 ?
Well, I think the best solution would be that it added the missing value to the bottom of column A of sheet2 (as a new post) and continued from there.
I'm sure someone out there can save me a couple of hours of trial & error.