copy/paste without highlighted cells

Discussion in 'Microsoft Excel Programming' started by Fan924, Feb 27, 2009.

  1. Fan924

    Fan924 Guest

    The macro below leaves two sheets with a highlighted column on
    each.How do I do this copy/paste without selecting data leaving both
    sheets without any highlighted cells?

    Sub WriteMap()
    Dim MySheet As Variant
    Dim MyTarget As Variant
    MySheet = ActiveSheet.Name
    MyTarget = Range("A1").Value
    Range("S2:S293").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("C" & MyTarget).Select
    Selection.PasteSpecial Paste:=xlValues
    Sheets(MySheet).Select
    End Sub
     
    Fan924, Feb 27, 2009
    #1
    1. Advertisements

  2. Fan924

    Mike H Guest

    Hi,

    Try this

    Sub WriteMap()
    Dim MySheet As String
    Dim MyTarget As Long
    MySheet = ActiveSheet.Name
    MyTarget = Range("A1").Value
    Range("S2:S293").Copy
    Sheets("Sheet1").Range("C" & MyTarget).PasteSpecial Paste:=xlValues
    Application.Goto Worksheets("Sheet1").Range("A1"), True
    Sheets(MySheet).Select
    End Sub

    "Fan924" wrote:

    > The macro below leaves two sheets with a highlighted column on
    > each.How do I do this copy/paste without selecting data leaving both
    > sheets without any highlighted cells?
    >
    > Sub WriteMap()
    > Dim MySheet As Variant
    > Dim MyTarget As Variant
    > MySheet = ActiveSheet.Name
    > MyTarget = Range("A1").Value
    > Range("S2:S293").Select
    > Selection.Copy
    > Sheets("Sheet1").Select
    > Range("C" & MyTarget).Select
    > Selection.PasteSpecial Paste:=xlValues
    > Sheets(MySheet).Select
    > End Sub
    >
     
    Mike H, Feb 27, 2009
    #2
    1. Advertisements

  3. Fan924

    Revolvr Guest

    On Feb 27, 12:19 pm, Fan924 <> wrote:
    > The macro below leaves two sheets with a highlighted column on
    > each.How do I do this copy/paste without selecting data leaving both
    > sheets without any highlighted cells?
    >
    > Sub WriteMap()
    > Dim MySheet As Variant
    > Dim MyTarget As Variant
    >     MySheet = ActiveSheet.Name
    >     MyTarget = Range("A1").Value
    >     Range("S2:S293").Select
    >     Selection.Copy
    >     Sheets("Sheet1").Select
    >     Range("C" & MyTarget).Select
    >     Selection.PasteSpecial Paste:=xlValues
    >     Sheets(MySheet).Select
    > End Sub


    After you do the copy, add something like Range("A1").Select
    You still have something selected but its only 1 cell, hardly
    noticeable.
    I don't know a way to unselect everything.
     
    Revolvr, Feb 27, 2009
    #3
  4. Fan924

    Fan924 Guest

    On Feb 27, 1:36 pm, mdmackillop <>
    wrote:
    > As you are doing a PasteSpecial Values, instead try
    > Sub WriteMap()
    > Dim Rng As Range
    > Set Rng = Range("S2:S293")
    > Sheets("Sheet1").Range("C" &
    > Range("A1")).Resize(Rng.Cells.Count).Value = Rng.Value
    > End Sub
    >
    > --
    > mdmackillop
    > ------------------------------------------------------------------------
    > mdmackillop's Profile:http://www.thecodecage.com/forumz/member.php?userid=113
    > View this thread:http://www.thecodecage.com/forumz/showthread.php?t=69236


    Exactly what I was looking for mdmackillop, thanks.
     
    Fan924, Feb 28, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Michel

    How copy format, font, color and border without copy/paste?

    Michel, Nov 5, 2003, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    765
    John Wilson
    Nov 5, 2003
  2. Gunnar Johansson

    Copy format without copy & paste

    Gunnar Johansson, Jun 30, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    469
    Gunnar Johansson
    Jul 1, 2004
  3. Guest

    Copy/paste range without resizeing cells

    Guest, Feb 11, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    261
    David Fam?
    Feb 11, 2005
  4. Martin

    Macro to copy highlighted cells to csv file

    Martin, Mar 14, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    232
    Jim Rech
    Mar 14, 2006
  5. Guest

    copy range of cells with blanks then paste without blanks

    Guest, Sep 3, 2006, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    589
    Guest
    Sep 3, 2006
  6. Guest

    Copy, paste, sort, copy paste

    Guest, Feb 28, 2007, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    708
    Guest
    Feb 28, 2007
  7. Rikkart

    paste values without copy-paste values

    Rikkart, Mar 15, 2010, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    379
    Dave Peterson
    Mar 15, 2010
  8. ryguy7272
    Replies:
    11
    Views:
    1,323
    Mike S
    Aug 6, 2010
Loading...