PDA

View Full Version : Deselect range after Macro runs



IgnBan
05-07-2008, 10:27 AM
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?


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

mdmackillop
05-07-2008, 10:43 AM
The simplest way I've found is to do a copy/pastespecial/all on the chosen cell

Norie
05-07-2008, 10:53 AM
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

Application.CutCopyMode = False

I also wonder what you are doing here.

i = 1

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

If you want to find the last row of data, or the next empty row there are plenty of other ways.

Here's one.

LastRow = ActiveSheet.Cells(Rows.Count,1).End(xlUp).Row

Another suggestion - create a reference to the workbook when you open it.

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

Similarly you can create a reference to the workbook the code is in like this.

Set wbThis = ThisWorkbook

These can now be used in subsequent code when ever you need to refer to either of the workbooks.

mdmackillop
05-07-2008, 11:46 AM
I asked the same question here (http://www.vbaexpress.com/forum/showthread.php?t=18922)

Zack Barresse
05-07-2008, 04:50 PM
Another suggestion - create a reference to the workbook when you open it.

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

Similarly you can create a reference to the workbook the code is in like this.

Set wbThis = ThisWorkbook

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...
Public Function ISWBOPEN(wbName As String) As Boolean
'Originally found by Jake Marx
On Error Resume Next
ISWBOPEN = Len(Workbooks(wbName).Name)
End Function

Cyberdude
05-11-2008, 10:32 AM
Hey, Zack, regarding the utility ISWBOPEN you just posted:





Public Function ISWBOPEN(wbName As String) As Boolean


'Originally found by Jake Marx


On Error Resume Next


ISWBOPEN = Len(Workbooks(wbName).Name)




End Function
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??

Bob Phillips
05-11-2008, 12:57 PM
No, exiting the function will reset the error handler.