It is always so hard to describe things. What was clear in my mind - is obviously not clear to another!
If I can get this to work - this macro will actually be in the middle of a much larger macro that actually works (except this one important step)!
Sheet 1 has data. What shows up on Sheet 1 is a result of some look ups. Not part of a Macro at all. It is dog information. Name and Owner (and a few other bits). So the user keys in a number and up pops all the information from the database about that dog. How many lines pop up could be 1 or any number up to about 250.
The Macro I have copies these lines to another sheet. Sheet 2. Sheet 2 will have columns A to G. And as I said - an undetermined number of lines. As many lines as there are records for the dog in the database.
Sheet 2 only has the lines copied from Sheet 1. Meaning there are no lines with spaces or blanks. Only the results from page 1 are on page 2. They get copied in by my Macro.
So now we are on sheet 2. Lets for my example say we copied in 11 rows.
I need the value in E1 and F1 to be copied down 10 times (11-1). So that all 11 rows have the same value in Column E and F.
The values in the other columns can not be change.
How many times do we have to copy? The same number of times as there are rows in sheet 2.
To illustrate. Below are the values in E and F only (example 4 rows)
The above is what came to sheet 2 via a copy.
I want the result to be:
Each time the Macro runs will have a different number of rows to copy down. And of course if there is only one row (which is possible!!) there is not need to copy anything as the correct information is in that row.
I hope this is a better explanation.
Thank you for your time!!