Deselect range after Macro runs

I have this macro to transfer a data range. After the macro runs the selected range still shows the range copied selected.
I wrote the If statement to see if this will move the focus by selecting cell P59, but the copied range still is show the contour active.
How can I modify the macro to deselect this range after the macro runs?


[VBA]Private Sub CommandButton1_Click()
Dim strWbkName As String
Dim i As Integer
Dim MyDate As String
MyDate = Date
Application.ScreenUpdating = False
strWbkName = ActiveWorkbook.Name

Workbooks.Open (ActiveWorkbook.Path & "\heatmapleak.xls")

i = 1

Do While ActiveWorkbook.ActiveSheet.Cells(i, 1).Value <> ""
i = i + 1
Loop

Windows(strWbkName).Activate
Sheets("WT_report_sheet_PM").Range("D56:AR56").Copy

Windows("heatmapleak.xls").Activate
Range(Cells(i, 4), Cells(i, 44)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = 1

Do While ActiveWorkbook.ActiveSheet.Cells(i, 1).Value <> ""
i = i + 1
Loop

ActiveWorkbook.ActiveSheet.Cells(i, 1).Value = strWbkName
ActiveWorkbook.Close (True)
Application.ScreenUpdating = True
If MsgBox("Data has been transfered succesfully", vbOKOnly, "Water Test") = vbOK Then
ActiveWorkbook.Activate
Range("P59").Select
End If
[/VBA]

The simplest way I've found is to do a copy/pastespecial/all on the chosen cell

Couple of points.

1 Don't use all that Activate/Active(Cell/Sheet/Workbook) stuff.

2 Don't use Select in the first place.

And try this at the end of the code
[vba]
Application.CutCopyMode = False
[/vba]
I also wonder what you are doing here.
[vba]
i = 1

Do While ActiveWorkbook.ActiveSheet.Cells(i, 1).Value <> ""
i = i + 1
Loop
[/vba]
If you want to find the last row of data, or the next empty row there are plenty of other ways.

Here's one.
[vba]
LastRow = ActiveSheet.Cells(Rows.Count,1).End(xlUp).Row
[/vba]
Another suggestion - create a reference to the workbook when you open it.
[vba]
Set wbOpen = Workbooks.Open (ActiveWorkbook.Path & "\heatmapleak.xls")
[/vba]
Similarly you can create a reference to the workbook the code is in like this.
[vba]
Set wbThis = ThisWorkbook
[/vba]
These can now be used in subsequent code when ever you need to refer to either of the workbooks.

I asked the same question here

Quote:

---

Originally Posted by Norie

Another suggestion - create a reference to the workbook when you open it.
[vba]
Set wbOpen = Workbooks.Open (ActiveWorkbook.Path & "\heatmapleak.xls")
[/vba]
Similarly you can create a reference to the workbook the code is in like this.
[vba]
Set wbThis = ThisWorkbook
[/vba]
These can now be used in subsequent code when ever you need to refer to either of the workbooks.

---

Another good test would be to test if the workbook was open or not...
[vba]Public Function ISWBOPEN(wbName As String) As Boolean
'Originally found by Jake Marx
On Error Resume Next
ISWBOPEN = Len(Workbooks(wbName).Name)
End Function[/vba]

Hey, Zack, regarding the utility ISWBOPEN you just posted:

[VBA]

Public Function ISWBOPEN(wbName As String) As Boolean


'Originally found by Jake Marx


On Error Resume Next


ISWBOPEN = Len(Workbooks(wbName).Name)

End Function
[/VBA]Should the statement "On Error GoTo 0" precede the "End Function" statement?? If something isn't done to close the "On Error" condition, won't it be inherited by the calling macro??

No, exiting the function will reset the error handler.