Forum Discussion

eesamp's avatar
eesamp
Copper Contributor
Dec 08, 2020

How to use vba to unselect a row after use

I have a workbook with multiple sheets. On the master sheet, I "find" a name, the run a vba application to use the data in the row selected by the "find." When that application completes, it returns ...
excel
Jihad Al-Jarady's avatar
Jihad Al-Jarady
MCT
Dec 08, 2020
Hi,

Would you please post the VBA here, so we can check it.
eesamp's avatar
eesamp
Copper Contributor
Dec 09, 2020

Jihad Al-Jarady 

A preface, I use the standard "Find" function to locate a member record on the "master" sheet," then hit a macro button to run my routine. When that finishes up, I return tho the "master" sheet at the same record. It is still selected by row. I need fot this to become "UnSelected" but still showing. (For now I have it returning at the top in "D2". Here is the code for the macro...

 

Sub ToPrSheet()
'
' ToPrSheer Macro
' ***USES THE STANDARD "fIND" TO GET THE MEMBER RECORD AND GET TO HERE***
' Only one label at a time can use this method. Any more wil need
' the use of the MailMerge function of a Word document
'
' Copies one row to PrSheet for printing label (eventually)
' This gets the active row on from "HPRAmasterDB2" sheet...
' copies it to row A on "PrSheet"...
' formats the Name, Address and LDP of the member...
' then sets up for printing to a label printer
' A printer list is provided to select the proper one

Dim rownumber As Integer
Worksheets("HPRAmasterDB2").Activate
' Set the row number and fall through for now - may add some procesing later...
rownumber = ActiveCell.Row
If ActiveCell.Value <> "" Then
End If
' Select and copy the row
Rows(rownumber).Select
Selection.Copy
' Activate thr "Printing sheet" and point to cell "A1"
Sheets("PrSheet").Select
Range("A1").Select
' Fill row "A" starting with cell "A1"
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste 'Columns A2:A4 auto format the mailing label
' Set column "A" to 36 points wide and set the print area...
Worksheets("PrSheet").Range("A1").ColumnWidth = 36
Worksheets("PrSheet").PageSetup.PrintArea = "$A$2:$A$4"
' Select the proper printer from the list (ie: A Label Printer)...
Application.Dialogs(xlDialogPrinterSetup).Show
' Preview it...
Worksheets("PrSheet").PrintPreview
' ...and Print the label
' TURNED OFF DURING DEBUGGING TO SAVE LABELS
' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
' Return to master sheet
Worksheets("HPRAmasterDB2").Activate 'and un select the highlighted row but stay there
Range("D2").Select 'temporary until an "UnSelect Row method is found

End Sub

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    MCT
    Dec 09, 2020

    eesamp

     

    Add this code at the end :

    Application.CutCopyMode 

     

     

     

    Sub ToPrSheet()
    '
    ' ToPrSheer Macro
    ' ***USES THE STANDARD "fIND" TO GET THE MEMBER RECORD AND GET TO HERE***
    ' Only one label at a time can use this method. Any more wil need
    ' the use of the MailMerge function of a Word document
    '
    ' Copies one row to PrSheet for printing label (eventually)
    ' This gets the active row on from "HPRAmasterDB2" sheet...
    ' copies it to row A on "PrSheet"...
    ' formats the Name, Address and LDP of the member...
    ' then sets up for printing to a label printer
    ' A printer list is provided to select the proper one

    Dim rownumber As Integer
    Worksheets("HPRAmasterDB2").Activate
    ' Set the row number and fall through for now - may add some procesing later...
    rownumber = ActiveCell.Row
    If ActiveCell.Value <> "" Then
    End If
    ' Select and copy the row
    Rows(rownumber).Select
    Selection.Copy
    ' Activate thr "Printing sheet" and point to cell "A1"
    Sheets("PrSheet").Select
    Range("A1").Select
    ' Fill row "A" starting with cell "A1"
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste 'Columns A2:A4 auto format the mailing label
    ' Set column "A" to 36 points wide and set the print area...
    Worksheets("PrSheet").Range("A1").ColumnWidth = 36
    Worksheets("PrSheet").PageSetup.PrintArea = "$A$2:$A$4"
    ' Select the proper printer from the list (ie: A Label Printer)...
    Application.Dialogs(xlDialogPrinterSetup).Show
    ' Preview it...
    Worksheets("PrSheet").PrintPreview
    ' ...and Print the label
    ' TURNED OFF DURING DEBUGGING TO SAVE LABELS
    ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ' Return to master sheet
    Worksheets("HPRAmasterDB2").Activate 'and un select the highlighted row but stay there
    Range("D2").Select 'temporary until an "UnSelect Row method is found

     

    Application.CutCopyMode 
    End Sub

    • eesamp's avatar
      eesamp
      Copper Contributor
      Dec 09, 2020

      Jihad Al-Jarady 

      That just stops the cutcopymode but leaves the row highlighted.

       

      Also, Application.cutcopymode needs an argument, either True, False or Status

      • adversi's avatar
        adversi
        Iron Contributor
        Dec 09, 2020

        eesamp 

        You have to declare it false in the beginning and make it true in the end. See below an example:

         

        Option Explicit
Sub Test()
Application.CutCopyMode = False

Range("E1:E20").copy
Application.Goto Range("A1")

Application.CutCopyMode = True
End Sub

         

Resources