Search code examples
excelvbacopycopy-paste

copy paste loop to 1 colomn 1 colomn

enter image description here

Sub Macro2()
Range("D3").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("B3").Select
ActiveSheet.Paste
Range("B4").Select
ActiveSheet.Paste
Range("B5").Select
ActiveSheet.Paste
Range("B6").Select
ActiveSheet.Paste
Range("B7").Select
ActiveSheet.Paste
Range("B8").Select
ActiveSheet.Paste
Range("B9").Select
ActiveSheet.Paste
End Sub

how code vba excel for copy paste 1 colomn 1 colomn (not copy area) with COUNTA. enter link description here

Solution

  • Try this line

    Sub Test()
Range("B2").Resize(Range("D2").Value).Value = Range("D3").Value
End Sub

    Or do you mean something like that

    Sub Test()
Range("B8").Select
Do While ActiveCell.Value <> ""
    ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Resize(Range("D2").Value).Value = Range("D3").Value
Application.CutCopyMode = False
End Sub