copy-paste via macro

[Boris7265]

Hi,

I am working with very large spreadsheet( 20000 and more rows) and I am trying to optimize some copy -paste operations. I need to put a formula in cell H4 that is "=CONCATENATE(B4," / ",D4)"-to join cells b4 and d4 together separted by "/", paste it to cell H5 as paste-value and copy cells H4:H6 (cell h6 is blank) and paste it down until the end of the rows.I can do it in the spreadsheet itself, but it so hard and time comsuming and prone for mistake so I have to redo it over and over due to large nimber or rows .The below the code that I am using , the probelm the first part is woking , but the secong part when the code suposted to paste H4-H6 down is not working.
Will be very greatfull for any advice.

Thank you ,

The Best Regards,

BorisGomel




Range("H4").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" / "",RC[-4])"
Range("H4").Select
Selection.Copy
Range("H5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("H4:H6").Select
Selection.Copy
Range("H7:H25000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[John Davis]

Hi,

I am working with very large spreadsheet( 20000 and more rows) and I am trying to optimize some copy -paste operations. I need to put a formula in cell H4 that is "=CONCATENATE(B4," / ",D4)"-to join cells b4 and d4 together separted by "/", paste it to cell H5 as paste-value and copy cells H4:H6 (cell h6 is blank) and paste it down until the end of the rows.I can do it in the spreadsheet itself, but it so hard and time comsuming and prone for mistake so I have to redo it over and over due to large nimber or rows .The below the code that I am using , the probelm the first part is woking , but the secong part when the code suposted to paste H4-H6 down is not working.
Will be very greatfull for any advice.

Thank you ,

The Best Regards,

BorisGomel




Range("H4").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" / "",RC[-4])"
Range("H4").Select
Selection.Copy
Range("H5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Range("H4:H6").Select
Selection.Copy
Range("H7:H25000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Hi Boris,

Does this help?

Sub Boris7265()

Dim i As Long


Range("H4").Value = Range("B4").Value & "/" & Range("D4").Value
Range("H5").Value = Range("H4").Value


For i = 25000 To 7 Step -3

    Range(Range("H" & i), Range("H" & i + 2)).Value = Range("H4:H6").Value
    
Next i



End Sub

 

[Boris7265]

Thank you so mich sir!!!

I don't how to thank you .

It works very well and so much easy my job.

Just one small very small quetion. The numbers of the row in the srpeadsheet very diiferrent every time ( we do upload it from Oracle) and I would like to copy paste until last row in column A -column A is always populated. Here is the code that I come up so far. i AM trying to integate lastrow in your code to copy paste unitl last rom in column A

Dim LASTROW As Long
LASTROW = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long


Range("H4").Value = Range("B4").Value & "/" & Range("D4").Value
Range("H5").Value = Range("H4").Value


For i = lastrow To 7 Step -3

Range(Range("H" & i), Range("H" & i + 2)).Value = Range("H4:H6").Value

Next i

Thank you for advise,

The Best Reagards,

BorisGomel

 

[John Davis]

Thank you so mich sir!!!

I don't how to thank you .

It works very well and so much easy my job.

Just one small very small quetion. The numbers of the row in the srpeadsheet very diiferrent every time ( we do upload it from Oracle) and I would like to copy paste until last row in column A -column A is always populated. Here is the code that I come up so far. i AM trying to integate lastrow in your code to copy paste unitl last rom in column A

Dim LASTROW As Long
LASTROW = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long


Range("H4").Value = Range("B4").Value & "/" & Range("D4").Value
Range("H5").Value = Range("H4").Value


For i = lastrow To 7 Step -3

Range(Range("H" & i), Range("H" & i + 2)).Value = Range("H4:H6").Value

Next i

Thank you for advise,

The Best Reagards,

BorisGomel

You're welcome Boris, from what I see, the revisions you made should work, do they?

 

[Boris7265]

Thank you so much. iT is working nicely!!! I mistyped first the code, but I fixed it .I need to be carefull with typing code. It is making easy my job so much.

A millions thanks!!!

I will post more on forum since it is really working and helping me .

Really apreciated and a have a wonderfull day.

BorisGomel