VBA copy and paste code help

[michelangelo]

I am looking for some help to make the below code a bit more efficient/elegant. Any help much appreciated. (note this is a sample, I use the same code 5 or 6 times hence I'm looking to shorten!).

If it helps I have the active sheet stored as sht from earlier in the code. LastRow, Next_Free_Row and today_green_col are integers created earlier in the code.

ActiveSheet.Cells(LastRow, today_green_col).Select
ActiveSheet.Cells(LastRow, today_green_col).Copy
ActiveSheet.Cells(Next_Free_Row, today_green_col).Select
ActiveSheet.Cells(Next_Free_Row, today_green_col).Activate
ActiveSheet.Paste
​

Any help would be much appreciated!

 

[MrJoosten]

Hi michelangelo,

Since you are looking to shorten it, doesn't this work?
I don't think you need to select first.

ActiveSheet.Cells(LastRow, today_green_col).Copy
ActiveSheet.Cells(Next_Free_Row, today_green_col).Paste

I didn't test it, but let me know

 

[michelangelo]

Hi michelangelo,

Since you are looking to shorten it, doesn't this work?
I don't think you need to select first.

ActiveSheet.Cells(LastRow, today_green_col).Copy
ActiveSheet.Cells(Next_Free_Row, today_green_col).Paste

I didn't test it, but let me know

Unfortunately not . Thanks for the suggestion though

 

[decadence]

Hi Michelangelo, Can you show us the rest of the code so we can understand better.

 

[steve the fish]

Does this do it?

sht.Cells(Next_Free_Row, today_green_col).Value = sht.Cells(LastRow, today_green_col).Value

 

[michelangelo]

Hi decadence,

there are about 400 lines of code around this, but i have put the pertiment ones in;

Dim LastRow As Integer
Dim Next_Free_Row As Integer
Dim today_green_col As Integer

Set sht = ThisWorkbook.Worksheets("Records")

'find last row
sht.UsedRange 'Refresh UsedRange
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).row
Next_Free_Row = LastRow + 1

sht.Select
ActiveSheet.Cells(LastRow, today_green_col).Select
ActiveSheet.Cells(LastRow, today_green_col).copy
ActiveSheet.Cells(Next_Free_Row, today_green_col).Select
ActiveSheet.Cells(Next_Free_Row, today_green_col).Activate
ActiveSheet.Paste

I hope this is emough. Also I realise I didnt state in the original post, what I really want to copy is the formulas and formatting from these cells.

thanks

 

[michelangelo]

Apologies i should have said as below, I am trying to copy the formulas and format, so this doesn't work. Thanks for the help anyway

 

[steve the fish]

Maybe:

sht.Cells(LastRow, today_green_col).Copy: sht.Cells(Next_Free_Row, today_green_col).PasteSpecial xlPasteFormulasAndNumberFormats