Copy Paste Macro - Which goes to next blank row.

[jad8688]

Hi,

I am currently trying to create a macro that copies & paste specials into a row and then repeats this process into the row below and so on...

To clarify;

The macro would copy cell's A64:B64 then paste special (Values) into cell's D10:E10.

This process would then be repeated into the next blank row.

e.g Copy cell's A64:B64 then paste special (Values) in cell's D11:E11...

This would then continue with the macro finding the next blank cell below.

If anyone could help with this matter It would be greatly appreciated!

Thanks,
James

 

[JLGWhiz]

Hi,

I am currently trying to create a macro that copies & paste specials into a row and then repeats this process into the row below and so on...

To clarify;

The macro would copy cell's A64:B64 then paste special (Values) into cell's D10:E10.

This process would then be repeated into the next blank row.

e.g Copy cell's A64:B64 then paste special (Values) in cell's D11:E11...

This would then continue with the macro finding the next blank cell below.

If anyone could help with this matter It would be greatly appreciated!

Thanks,
James

Hi James, the macro is pretty simple to write, but you need to set some limits on how many times you want to copy A64:B64, otherwise it would just go on until the end of the column. So lets say you want to repeat the process for 10 rows:

Sub James()
For i = 10 To 19
 Range("A64:B64").Copy
 Range("D" & i).PasteSpecial xlPasteValues
Next
End Sub

 

[kevatarvind]

something like below

Sub Test()
Range("A64:B64").Copy
Range("D" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

 

[jad8688]

Hi JLG,

Thanks for the quick response!

Is there any way to do it so it copies the cells one at a time?

For example it would copy A64:B64 then paste it in the D10:E10. Then if later the contents of the cell A64:B64 changes, you could run the macro again it would then copy the different output into D11:E11 or the next blank rows?

Thanks,
James

 

[jad8688]

Yes Kevatarvind this is the one! It works great...

Thank you both for your responses!

Cheers,
James

 

[rockyw]

I use this to copy paste one cell at a time. Set like this it copies (top row of code) Q5 to B235 then to, cell C235, then D235 then E235 ect. You can vary the offset and number of columns to paste to. Hope it helps.

Sub Macro1()

Application.ScreenUpdating = False

'SHP Net Production
Range("E235").End(xlToLeft).Offset(, IIf(Range("E235").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q5").Formula
Range("E189").End(xlToLeft).Offset(, IIf(Range("E189").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q6").Formula
Range("E143").End(xlToLeft).Offset(, IIf(Range("E143").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q7").Formula
Range("E96").End(xlToLeft).Offset(, IIf(Range("E96").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q8").Formula
Range("E49").End(xlToLeft).Offset(, IIf(Range("E49").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q9").Formula
Range("E3").End(xlToLeft).Offset(, IIf(Range("E3").End(xlToLeft).Value = "", 1, 1)).Formula = Range("Q10").Formula
Application.ScreenUpdating = True

End Sub

This part copies Q 5, 6, 7, 8, 9 and 10 to the rows I need the data. I have about 200 rows of code. I works fine for what I do. Good Luck

 

[sparklerise]

I had the same questions. When I 'paste special"-formula, the outcome is the error #VALUE. Why?