Code to insert a x number of rows and copy paste values

I have the following code that will insert a number of values based on another sheet number of cells. It finds the next blank cell in range(like Ctrl+Down) and inserts them. I want to paste the values in those new inserted rows, but I don't seem to manage that. Also would be possible to find and copy only the formulas from row above in these newly inserted rows(only the formulas, without the cells containing values. I have a mix of formulas and values in the cells above)

Please Login or Register  to view this content.

And drumroll...

Your sample file is where

Hi sintek, I've attached a sample. The part in yellow is the desired results after macro

Formulas are in last 3 columns only...is this what you mean...
By the way...calculation is currently set to manual...

Please Login or Register  to view this content.

The formula will always be in the last columns, but my last columns with formula will be dynamic. Using another macro, each time that one is run it will copy the range with formulas, will insert them again in the last column and paste the columns previously copied as values. Meaning that I cannot use the column title.

EDIT...Now I get it...
it must make use of the last 3 columns always...

See amended code above...Will always use last 3 columns...

The R-T columns in green will be moved in columns U-W when my other macro is run
That means that they will be pasted as formats and formulas
Then the macro will go back to R-T and paste them as values
And this will happen all the time.E.g U-W will go to X-Z the second time macro is run and so on
In this case, only the last 3 columns will contain formulas that needs to be pasted to the newly inserted rows
Therefore, the macro that you help me with has to find the last 3 column (these will be dynamically moved) formulas and copy them down to the new inserted rows

Therefore, the macro that you help me with has to find the last 3 column (these will be dynamically moved) formulas and copy them down to the new inserted rows

As per post 6 the newly amended code does just that...

Hi again. The formula part works as intended. The copy/paste from J column presents an issue. The thing is that in J3 I have a title. Then in J5 and down, I have the data. Right now your macro copies the title from J3 3 times in my second sheet(I have title and 2 more values in column J). I tried using range J5 & lastrow but it doesn't do anything.
Any ideea on how I may overcome this?

Then in J5 and down

So is the copy range always from J5:J & lastrow of column J?
If so set rng to J5:J & lastrow then this should solve...or am i missing something else...
Apologies for not quite understanding...Your sample file only had data from J5:J7
and this snippet of your code did not reference J3 at all...

Please Login or Register  to view this content.

See attached file...


i now see your problem...

Please Login or Register  to view this content.

should be...

Please Login or Register  to view this content.

Absolutely perfect. Many thanks for your help and your fast replies. You are the man!

.........................

THANKS.gif