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

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

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.

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/sh...ad.php?t=69236

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/sh...ad.php?t=69236

Exactly what I was looking for mdmackillop, thanks.