I have this code in my file.
It selects everything in the sheet and copies it and then pastes back to values only.
Is there any way to deselect everything when its done?
Any way to deselect a select in VBA?
- GreasySpot
- Closed
-
-
-
Re: Any way to deselect a select in VBA?
Hi there,
There are 2 simple ways to achieve this, first, don't select the sheet to start with
or, just select another range when done -
Re: Any way to deselect a select in VBA?
I have it scrolling thru all sheets. I can't reference the sheet your way can I? ie: ("Sheet1") Doesn't this specifically call it by name? I don't want to do it that way.
-
Re: Any way to deselect a select in VBA?
Show us the whole code or explain what you want to do.
-
-
Re: Any way to deselect a select in VBA?
Display MoreCode- Sub Formula_Zapper_MkII()
- Dim sh As Worksheet, HidShts As New Collection
- For Each sh In ActiveWorkbook.Worksheets
- If Not sh.Visible Then
- HidShts.Add sh
- sh.Visible = xlSheetVisible
- End If
- Next sh
- Worksheets.Select
- Cells.Select
- Selection.Copy
- Selection.PasteSpecial Paste:=xlPasteValues
- ActiveSheet.Select
- Application.CutCopyMode = False
- For Each sh In HidShts
- ' sh.Delete
- sh.Visible = xlSheetHidden
- Next sh
- End Sub
This is what I am using. All cells on each are selected when I this is finished running. There is not any problem just a nuisance to me.
I just wanted to unselect everything when it is thru.
-
-
Re: Any way to deselect a select in VBA?
Is that supposed to replace the loop or the whole code?
-
Re: Any way to deselect a select in VBA?
It has a compile error on the with statement.
-
Re: Any way to deselect a select in VBA?
sorry typo - remove the dot after ws.UsedRange.
Display MoreCodeThat should replace the whole code. Also got rid of the If statement - not needed.
-
Re: Any way to deselect a select in VBA?
I put your code in as below but it didn't change anything as far as the selection goes.
Display MoreCode- Option Explicit
- Sub Formula_Zapper_MkII()
- Dim ws As Excel.Worksheet
- For Each ws In ActiveWorkbook.Worksheets
- If Not ws.Visible Then
- With ws.UsedRange
- .Value = .Value
- End With
- End If
- 'Dim sh As Worksheet, HidShts As New Collection
- 'For Each sh In ActiveWorkbook.Worksheets
- ' If Not sh.Visible Then
- ' HidShts.Add sh
- ' sh.Visible = xlSheetVisible
- 'End If
- 'Next sh
- Worksheets.Select
- Cells.Select
- Selection.Copy
- Selection.PasteSpecial Paste:=xlPasteValues
- ActiveSheet.Select
- Application.CutCopyMode = False
- 'For Each sh In HidShts
- ' ' sh.Delete
- ' sh.Visible = xlSheetHidden
- 'Next sh
- Next ws
- End Sub
-
Re: Any way to deselect a select in VBA?
Don't add it into anything - it's supposed to entirely replace all of your previous code. Run it on it's own and then go check your sheets, you will see that everything is replaced with values - even on hidden sheets.
-
Re: Any way to deselect a select in VBA?
It doesn't do anything on its on.
-
Re: Any way to deselect a select in VBA?
It won't change sheets, or make the screen change in anyway. But if you run it and then check the used range on each sheet, all the formulas will be replaced with their respective values - the same as if you did a copy -> paste special -> values.
-
Re: Any way to deselect a select in VBA?
That's a big negative.
-
Re: Any way to deselect a select in VBA?
Can you attach an example of your data and how you're running the code? This is a very basic piece of code, but I did test anyway before posting and had no issues.
-
Re: Any way to deselect a select in VBA?
Actually I can't do that because it has our cost data and prices and such on it. However it wouldn't do you much good because all of the data is pulled in from other files. I used the addin Morefunc so I can access data from files that are closed. This is a formula in one cell.
Code- {=IF(ROWS(A$6:A6)>'C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$6,"",INDEX('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,SMALL(IF(FREQUENCY(IF('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15<>"",MATCH('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,'C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15,0)),ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15)-ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9)+1),ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9:$H$15)-ROW('C:\Work\Plants\Cary\Plant Costs\[Aggregate Usage Week Ending 4-11-15.xlsm]Monday'!$H$9)+1),ROWS(A$6:A6))))}
-
Re: Any way to deselect a select in VBA?
Without an example there's not really much more that I can advise. The code that I posted loops through each worksheet in the active workbook and updates every cell in the used range with the value it currently displays.
-
Re: Any way to deselect a select in VBA?
Would it matter if the cells were locked? The sheet is not protected.
I will send you the file but I don't want it posted on the board. How can I get it to you?
-
Re: Any way to deselect a select in VBA?
locking cells doesn't take effect until the worksheet is protected so that wouldn't make any difference.
Can you not upload the file and just remove and personal/identifiable data from it? The whole point of OzGrid is to provide free help to people that want to learn and sending things privately defeats that object - more importantly there are a vast amount of professionals and experts on here that may come across this and be able to provide a better solution so sending things privately means you're missing out on all that potential.
Alternatively you could set up a small sample to show what you're trying to achieve?