VBA De-select

[Katy Jordan]

Hi, the below retains the selection in worksheet array, how can i get the code to de-select

i tried adding .Range("A5").Select, but the selection is still in place

 With Worksheets(WSary(a)).Range("A6:M" & NR & ",Q6:R" & NR)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 0
        .Borders.Weight = xlHairline
       [COLOR=Red] .Range("A5").Select[/COLOR]
       End With

 

[ZVI]

Each sheet always has the selection. Selection can be only changed in a sheet but can’t be excluded from the sheet.
You can protect sheet with disabled cells selection options.
It will look like there is no selection, but this just hide it till unprotection

 

[Peter_SSs]

We may need to see the full code (if it isn't too long) to understand what is happening.

 

[Katy Jordan]

Each sheet always has the selection. Selection can be only changed in a sheet but can’t be excluded from the sheet.

Vlad, In the Worksheets(Wsary(a)) , i have ranges which are in selection, i stepped through code and found the selection took place after this copy and paste, i just cant get rid of the selection between the last copy and paste code

.Columns("A:C").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("A" & NR).PasteSpecial Paste:=xlPasteValues
      .Columns("F:O").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("D" & NR).PasteSpecial Paste:=xlPasteValues
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

With ShtRaw
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .AutoFilterMode = False
  With .Range("A5:O" & LR)
    For a = LBound(WSary) To UBound(WSary)
      .AutoFilter Field:=2, Criteria1:=WSary(a)
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      'Copy Data to destination worksheets
      On Error Resume Next
      .Columns("A:C").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("A" & NR).PasteSpecial Paste:=xlPasteValues
      .Columns("F:O").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("D" & NR).PasteSpecial Paste:=xlPasteValues
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      
      'Apply Formatting
      With Worksheets(WSary(a)).Range("A6:M" & NR & ",Q6:R" & NR)
        .Borders.LineStyle = xlNone
        .Borders.LineStyle = xlContinuous
        .Interior.ColorIndex = 0
        .Borders.Weight = xlHairline
       End With

 

[ZVI]

Katy, you can save old selection, then do something and at the end restore old selection like this:

Sub test()
  
  Dim OldSel As Range
  
  ' Save old selection
  Set OldSel = Selection
  
  ' Your code like this
  Range("A1:B20").Copy
  Range("D2:D21").PasteSpecial xlPasteValues
  Application.CutCopyMode = False ' <-- disable selection mode
  ' End of your code
  
  ' Restore old selection
  OldSel.Select
  
End Sub

 

[Katy Jordan]

Nope that did not work, note i have worksheet array, the application. cut does not work

WSary = Array("Sheet1", "Sheet2")

Katy, you can save old selection, then do something and at the end restore old selection like this:

Sub test()
  
  Dim OldSel As Range
  
  ' Save old selection
  Set OldSel = Selection
  
  ' Your code like this
  Range("A1:B20").Copy
  Range("D2:D21").PasteSpecial xlPasteValues
  Application.CutCopyMode = False ' <-- disable selection mode
  ' End of your code
  
  ' Restore old selection
  OldSel.Select
  
End Sub

 

[ZVI]

The same as above:

Sub Test1()
  
  With Selection  ' <-- Save old selection in a stack
    
    ' Your code like this
    Range("A1:B20").Copy
    Range("D2:D21").PasteSpecial xlPasteValues
    Application.CutCopyMode = False ' <-- Disable selection mode
    ' End of your code
    
    .Select       ' <-- Restore old selection from the stack
  
  End With
  
End Sub

 

[ZVI]

Nope that did not work, note i have worksheet array, the application. cut does not work

WSary = Array("Sheet1", "Sheet2")

Do save - restore selection for each processed sheet

 

[Katy Jordan]

Do save - restore selection for each processed sheet

I'm a bit confused, i can see what you did in your post above, but how do i implement your method to this as the data is copy from Rawdata and then pasting to Worksheet(Wsary(a))

.Columns("A:C").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("A" & NR).PasteSpecial Paste:=xlPasteValues
      .Columns("F:O").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
      Worksheets(WSary(a)).Range("D" & NR).PasteSpecial Paste:=xlPasteValues
      NR = Worksheets(WSary(a)).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

 

[ZVI]

I'd suggest to do saving - restoring of each sheet selection in the separate subrotines.
Just call SaveSelections ActiveWorkbook before your code and call RestoreSelections at the end.

The code:

Dim SelectionsCol As Collection

Sub Test3()
  
  ' Save selection of each sheet
  Application.ScreenUpdating = False
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  For Each Sh In Worksheets
    Sh.Activate
    Selection.Offset(10, 10).Select
  Next
  
  ' Restore each sheet selection
  RestoreSelections
  Application.ScreenUpdating = True

End Sub

Private Sub SaveSelections(Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each Sh In Wb.Worksheets
      Sh.Activate
      SelectionsCol.Add Selection
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Private Sub RestoreSelections()
  Dim x, ScUpd
  On Error Resume Next
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each x In SelectionsCol
      x.Parent.Activate
      x.Select
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
  Set SelectionsCol = Nothing
End Sub

 

[Katy Jordan]

Vlad, if i just use this after my code then it works, but your other method did not de-select, i tried as you suggested.

For Each Sh In Worksheets
Sh.Activate
Selection.Cells(1, 1).Select
Next

I'd suggest to do saving - restoring of each sheet selection in the separate subrotines.
Just call SaveSelections ActiveWorkbook before your code and call RestoreSelections at the end.

The code:

Dim SelectionsCol As Collection

Sub Test3()
  
  ' Save selection of each sheet
  Application.ScreenUpdating = False
  SaveSelections ActiveWorkbook
  
  ' Put your code here, below is just for testing
  For Each Sh In Worksheets
    Sh.Activate
    Selection.Offset(10, 10).Select
  Next
  
  ' Restore each sheet selection
  RestoreSelections
  Application.ScreenUpdating = True

End Sub

Private Sub SaveSelections(Wb As Workbook)
  Dim Sh As Worksheet, ScUpd
  Set SelectionsCol = New Collection
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each Sh In Wb.Worksheets
      Sh.Activate
      SelectionsCol.Add Selection
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
End Sub

Private Sub RestoreSelections()
  Dim x, ScUpd
  On Error Resume Next
  With Application
    ScUpd = .ScreenUpdating
    If ScUpd Then .ScreenUpdating = False
    For Each x In SelectionsCol
      x.Parent.Activate
      x.Select
    Next
    If ScUpd Then .ScreenUpdating = ScUpd
  End With
  Set SelectionsCol = Nothing
End Sub