Unable to paste data in Do While Loop using pastevalue function

[amardeep_s]

hi All,

i am new to this forum and newbie to the Excel VBA. I am trying to complie simple loop VBA code to copy paste data until it finds empty cell. Code function properly however i want data to be pasted in Value format. Below is the code. Appreciate if anyone could help me with this.

Sub Macro_Loop()
Range("A2").Select
Do While ActiveCell.Value <> Empty
Range("A2", Range("B2").End(xlDown)).Copy Range("K2:L2")
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Code that gives me error when i try to use paste value function:

Sub Macro_Loop()

Range("A2").Select
Do While ActiveCell.Value <> Empty
Range("A2", Range("B2").End(xlDown)).Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecials :=xlPasteValues
ActiveCell.Offset(1, 0).Select
Loop

End Sub

 

[Kamolga]

Hi,

Your error comes from the fact that when you paste, you paste more than one cell. So if next you ask him to get back to active cell, it does not work.

Your code seems strange to me as you will always copy-paste the same things at the the place whatever the stage of the loop. None the less you should not have an error using following :

[COLOR=#000000][FONT=Calibri]Sub Macro_Loop()
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Dim Rng as Range
Dim i as Long
i=2
Set Rng=Cells(i,1)
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Rng.Select
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Do While Rng.Value <> Empty
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Range("A2", Range("B2").End(xlDown)).Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecials :=xlPasteValues[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]
i=i+1
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]Loop
[/FONT][/COLOR][COLOR=#000000][FONT=Calibri]
[/FONT][/COLOR][COLOR=#333333]End Sub[/COLOR]

 

[amardeep_s]

Hi,

thanks for your reply. It does make sense of the error now. However, when i run your code it throws me syntax error on the pastevalue line.

Selection.PasteSpecials :=xlPasteValues

i tried and changed it to below but still it gives me error "Object doesnt support this method or property"

Selection.PasteSpecials Paste:=xlPasteValues

Any idea whats going wrong here??

 

[MARK858]

PasteSpecial not PasteSpecials

      Range("A2", Range("B2").End(xlDown)).Copy
        Range("K2").PasteSpecial xlPasteValues

but other parts of your code don't make much sense

 

[MARK858]

or....

With Range("A2", Range("B2").End(xlDown))
  Range("K2:L2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[amardeep_s]

thanks, i am able to achieve what i intended to with the help of With-EndWith functionality. No need to loop using Do-While which doesnt make sense here. Appreciate for your help and support