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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Copy/paste range without resizeing cells

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

    Macro to copy highlighted cells to csv file

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

    Copy, paste, sort, copy paste

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

    paste values without copy-paste values

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

Share This Page