Forum Discussion

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

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

MCT

Dec 09, 2020

eesamp

Add this code at the end :

Application.CutCopyMode

Application.CutCopyMode
End Sub

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
  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
  - eesamp
    Copper Contributor
    Dec 10, 2020
    Ok, I have solved the problem. here is the code now at the end of the PrSheet routine...
    
    ' ...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
    Application.CutCopyMode = True
    Range("F2").Select
    ActiveWindow.FreezePanes = True
    ' MsgBox rownumber
    ActiveSheet.Range("$A$" & rownumber).Select
    
    End Sub
- Jihad Al-Jarady
  MCT
  Dec 09, 2020
  eesamp
  
  It depends on the version you are using.
  You can set it to False.
  Application.cutcopymode =Flase