copy/paste without highlighted cells

F

Fan924

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
 
M

Mike H

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
 
R

Revolvr

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.
 
M

mdmackillop

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 Su
 
F

Fan924

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

Exactly what I was looking for mdmackillop, thanks.
 

Ask a Question

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. After that, you can post your question and our members will help you out.

Ask a Question

Top