Consulting

Results 1 to 7 of 7

Thread: Deselect range after Macro runs

  1. #1
    VBAX Contributor
    Joined
    Jan 2008
    Posts
    104
    Location

    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]

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    The simplest way I've found is to do a copy/pastespecial/all on the chosen cell
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    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.

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I asked the same question here
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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]

  6. #6
    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??

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No, exiting the function will reset the error handler.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •