Unselect column after pasting data

2
votes

this may be very simple but I am not able to work around it.

I could free up the column from where I am copying data by using Application.CutCopyMode=False but the column where data is pasted is still remain selected, I tried ActiveSheet.EnableSelection = xlNone as suggested in one of the forum without any success rest of my code working fine: 

Dim wb As Workbook
Dim t1 As Worksheet
Dim r As Worksheet

Set wb = ActiveWorkbook
Set t1 = Sheets("dataT1")
Set r = Sheets("HPV1report")

t1.Range("D3:CR5").Copy
r.Range("A2").PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=True
    Selection.EntireColumn.AutoFit
End sub

Other thing I can think of is selecting Cell A1 so the column will get free but I will prefer to use select if i can.

3
excelvba

3 Answers

5
votes

You can not really un-select a cell, because it is in the nature of a worksheet that there is always a selected cell.

The only reason why there might be no selected cell is

  • you select an object like a control/button, etc.
  • or to protect the worksheet against user interaction like @moosli pointed out.

So the correct way to go is, as you already found out:
Select any other cell, eg. A1.

Worksheets("MySheet").Range("A1").Select
0
votes

You can achieve it in this way

Application.ScreenUpdating = False
Worksheets("name sheet selected data").Activate
Worksheets("name sheet selected data").Range("A1").Select
Worksheets("name sheet you want goto").Activate
Application.ScreenUpdating = True
0
votes

As PEH pointed out, there is always something selected in Excel, so he proposes to select a hardcoded cell like A1. I prefer to store the cell that is active before the copy/paste process and reselect this cell after pasting.

Dim ActCell As Excel.Range
Set ActCell = Excel.Application.ActiveCell
'Do the copy and paste process

Excel.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut
ActCell.Select