Paste special code to be modified..

[VBABEGINER]

I want this line of code modify bit..
copy and paste as a paste special values..How do ​I make this..

Range("A3", Range("A3").End(xlDown)).Copy Range("AA3")

 

[steve the fish]

Use the macro recorder and it will show you.

 

[VBABEGINER]

Yes, I know that. But the line of code I have get it from mrexcel only. So wanted to see how can use in this scenario. Thanks for reply..

 

[steve the fish]

Ok show us the code from the macro recorder and ill explain it.

 

[VBABEGINER]

Ok show us the code from the macro recorder and ill explain it.

Hi, Thanks again for reply. Here is the macro recorder..

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("AA3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[steve the fish]

So you can simplify that down a bit as you rarely need to select anything. Taking your first two lines:

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select

The selection is Range("A3") so we can replace selection by Range("A3")

Also we dont need to select our new range to copy it so:

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

can be simplified down to the one liner:

Range(Range("A3"), Range("A3").End(xlDown)).Copy

To paste special is the same:

Range("AA3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("AA3") is the selection so:

Range("AA3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[VBABEGINER]

Hey, Thanks Really. This helps. I didn't think in that way before.

So you can simplify that down a bit as you rarely need to select anything. Taking your first two lines:

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select

The selection is Range("A3") so we can replace selection by Range("A3")

Also we dont need to select our new range to copy it so:

Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

can be simplified down to the one liner:

Range(Range("A3"), Range("A3").End(xlDown)).Copy

To paste special is the same:

Range("AA3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Range("AA3") is the selection so:

Range("AA3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False