+ Reply to Thread
Results 1 to 3 of 3

Copy non blank cells to another worksheet

  1. #1
    JD
    Guest

    Copy non blank cells to another worksheet

    HI, can anyone point out why this isn't working?

    Sub CopyDataOnly()

    Sheets("Sheet1").Range("A10:A100").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    SkipBlanks _
    :=False, Transpose:=False

    End Sub

    I end up getting an error after "Selection.Copy".

    Basically I'm looking to select all cells with data and move them to
    another worksheet and paste them.


  2. #2
    Norman Jones
    Guest

    Re: Copy non blank cells to another worksheet

    Hi JD,

    Try the following version which avoids selections:

    '=============>>
    Sub CopyDataOnly()
    On Error Resume Next
    Sheets("Sheet1").Range("A10:A100"). _
    SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("Sheet2").Range("A10").PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    On Error GoTo 0
    Application.CutCopyMode = False
    End Sub
    '<<=============

    Selections are rarely necessary or desirable.

    ---
    Regards,
    Norman



    "JD" <_antipodean_@bigpond.com> wrote in message
    news:1138754337.968245.189030@g14g2000cwa.googlegroups.com...
    > HI, can anyone point out why this isn't working?
    >
    > Sub CopyDataOnly()
    >
    > Sheets("Sheet1").Range("A10:A100").Select
    > Selection.SpecialCells(xlCellTypeConstants, 23).Select
    > Selection.Copy
    > Sheets("Sheet2").Select
    > Range("A10").Select
    > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
    > SkipBlanks _
    > :=False, Transpose:=False
    >
    > End Sub
    >
    > I end up getting an error after "Selection.Copy".
    >
    > Basically I'm looking to select all cells with data and move them to
    > another worksheet and paste them.
    >




  3. #3
    JD
    Guest

    Re: Copy non blank cells to another worksheet


    Norman Jones wrote:
    > Hi JD,
    >
    > Try the following version which avoids selections:
    >
    > '=============>>
    > Sub CopyDataOnly()
    > On Error Resume Next
    > Sheets("Sheet1").Range("A10:A100"). _
    > SpecialCells(xlCellTypeConstants, 23).Copy
    > Sheets("Sheet2").Range("A10").PasteSpecial _
    > Paste:=xlPasteValues, _
    > Operation:=xlNone, _
    > SkipBlanks:=False, _
    > Transpose:=False
    > On Error GoTo 0
    > Application.CutCopyMode = False
    > End Sub
    > '<<=============
    >
    > Selections are rarely necessary or desirable.


    Thanks Norman, that works a treat.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1