Excel Hints

Results 1 to 7 of 7

Thread: Deselect range after Macro runs

  1. #1

    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?


    VB:
    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 
    
    
    Formatting tags added by mark007

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

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    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
    VB:
    Application.CutCopyMode = False 
    
    
    Formatting tags added by mark007
    I also wonder what you are doing here.
    VB:
    i = 1 
     
    [COLOR=blue]Do[/COLOR] [COLOR=blue]While[/COLOR] ActiveWorkbook.ActiveSheet.Cells(i, 1).Value <> "" 
    i = i + 1 
    [COLOR=blue]Loop[/COLOR] 
    
    
    Formatting tags added by mark007
    If you want to find the last row of data, or the next empty row there are plenty of other ways.

    Here's one.
    VB:
    LastRow = ActiveSheet.Cells(Rows.Count,1).End(xlUp).Row 
    
    
    Formatting tags added by mark007
    Another suggestion - create a reference to the workbook when you open it.
    VB:
    Set wbOpen = Workbooks.Open (ActiveWorkbook.Path & "\heatmapleak.xls") 
    
    
    Formatting tags added by mark007
    Similarly you can create a reference to the workbook the code is in like this.
    VB:
    Set wbThis = ThisWorkbook 
    
    
    Formatting tags added by mark007
    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
    12,503
    Location
    I asked the same question here
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    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,835
    Location
    Quote Originally Posted by Norie
    Another suggestion - create a reference to the workbook when you open it.
    VB:
    Set wbOpen = Workbooks.Open (ActiveWorkbook.Path & "\heatmapleak.xls") 
    
    
    Formatting tags added by mark007
    Similarly you can create a reference to the workbook the code is in like this.
    VB:
    Set wbThis = ThisWorkbook 
    
    
    Formatting tags added by mark007
    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...
    VB:
    Public Function ISWBOPEN(wbName As String) As Boolean 
         'Originally found by Jake Marx
        On Error Resume Next 
        ISWBOPEN = Len(Workbooks(wbName).Name) 
    End Function 
    
    
    Formatting tags added by mark007

  6. #6
    Hey, Zack, regarding the utility ISWBOPEN you just posted:

    VB:
    [LEFT][LEFT]Public Function ISWBOPEN(wbName As String) As Boolean 
     
     
     'Originally found by Jake Marx
     
     
    On Error Resume Next 
     
     
    ISWBOPEN = Len(Workbooks(wbName).Name) 
     
     
    End Function 
    
    
    Formatting tags added by mark007
    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??[/LEFT]
    [/LEFT]

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,988
    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
  •