Cannot copy/specialpaste values only in macro

Question

Forum members,

I am up against a wall trying to copy and paste column values in a worksheet that serves as a template using a macro.  the columns I am working with use formulas that process text and numbers.  I am trying to copy the contents of the formula columns to other columns as values-only so they can be further processed by sorting, filtering, editing etc. also using macros.   Doing it manually works great.  Using ActiveSheet in the macro works but also copies the formulas that I don't want. Using PasteSpecial in the macro causes the debugger to show an error of "object doesn't support this property or method".  The code is as follows; 

'copy business name (this is text only)

    Range("A4:A4" & lrw).Select

    Selection.Copy

    Range("ak4:ak" & lrw).Select

    ActiveSheet.Paste

 'Selection.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

'copy street address data (this is both numbers and text)

'name and number

    Range("ab4:af" & lrw).Select

    Selection.Copy

    Range("al4:ap" & lrw).Select

 'ActiveSheet.Paste

    Selection.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

Any suggestions on resolving this problem are much appreciated.  I am running Excel2010 on a Windows7 OS.

derwalrosz

Answer 1

'copy business name (this is text only)

    Range("A4:A4" & lrw).Select

    Selection.Copy

    Range("ak4:ak" & lrw).Select

    ActiveSheet.Paste

Note that you left the 4 in the second part of the range. For a pseudo-Paste Special, Values this would be better written as,

Range("ak4:ak" & lrw) = Range("A4:A" & lrw).Value

'copy street address data (this is both numbers and text)

'name and number

    Range("ab4:af" & lrw).Select

    Selection.Copy

    

    Range("al4:ap" & lrw).Select

 'ActiveSheet.Paste

    Selection.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

As above, try:

Range("al4:ap" & lrw) = Range("ab4:af" & lrw).Value

Answer 2

Selection.Selection is not valid. Try this - it avoids selecting the cells:

    'copy business name (this is text only)

    Range("A4:A" & lrw).Copy

    Range("AK4").PasteSpecial Paste:=xlPasteValues

    'copy street address data (this is both numbers and text)

    'name and number

    Range("AB4:AF" & lrw).Copy

    Range("AL4").PasteSpecial Paste:=xlPasteValues

Answer 3

Selection.Selection is not valid. Try this - it avoids selecting the cells:

 

    'copy business name (this is text only)

    Range("A4:A" & lrw).Copy

    Range("AK4").PasteSpecial Paste:=xlPasteValues

    'copy street address data (this is both numbers and text)

    'name and number

    Range("AB4:AF" & lrw).Copy

    Range("AL4").PasteSpecial Paste:=xlPasteValues

Forgive me for taking so long to reply.  This code worked great.  You guys are doing great service to those of us who are unable to get individual help to learn the details of vba programming.  Thank you so much.

derwalrosz