copy/paste without highlighted cells

[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

 

[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

 

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

 

[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

 

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