Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    VBA Term for 'Select Sheet' (Excel 97)

    What's the term to select the activesheet in an Excel macro? I've tried Sheets.Select, ActiveSheet.Select and something else.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Perhaps .Activate, but if it's already active, you don't need to select it again, just act on it:

    With Activesheet
    .Method
    ...etc.

    Can you tell us more about the issue?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Thanks for such a speedy reply. I'd like to convert all the cells of all the sheets in a file from formulas to values. Short of that, I'd like to have a macro that just does one sheet at a time.

    Here's where I'm at:

    ' ConvertSheettoValues Macro

    With ActiveSheet
    Sheet.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A2").Select
    End With

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    <P ID="edit" class=small>(Edited by JohnBF on 31-Mar-04 18:20. Improved code.)</P>This should hit all sheets in the selected workbook and be relatively efficient. Let me know the details if it errors out:

    Sub SetAllSheetsToValues()
    Dim shtSheet As Worksheet, shtActive As Worksheet
    Dim rng As Range
    Application.ScreenUpdating = False
    Set shtActive = ThisWorkbook.ActiveSheet
    For Each shtSheet In ThisWorkbook.Sheets
    With shtSheet
    If .ProtectContents = False Then ' skip protected sheets
    On Error Resume Next
    Set rng = .UsedRange.SpecialCells(xlCellTypeFormulas, _
    xlErrors + xlLogical + xlNumbers + xlTextValues)
    Err.Clear
    If Not rng Is Nothing Then
    With rng
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    End With
    End If
    End If
    End With
    Next shtSheet
    shtActive.Activate ' reset to original active sheet
    [a2].Activate
    Set shtActive = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    John, I'm not sure how to help you help me with this.
    I copied the macro to my "personal" file and ran it from there. Next I copied it directly into the file I was working with; same results.
    The cursor jumps to A2 and I get the message "Now processing. . ." in the lower left corner of Excel. There are no error messages, yet I can move the cursor around. When I ran the macro from Personal, then Personal became the active file.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Sorry, change every reference from "ThisWorkbook" to "ActiveWorkbook".
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    This has gotta be close now. I forgot to mention before, but having seen it again, I notice that all cells that have formulas are highlighted in each sheet, but still not converted to values. Does that help?

  8. #8
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Navigator utilities has a button which does just what you want. From the Sheet Navigator, select which sheets you want to replace with values, or click on Select All, then click on paste values. One of the many features for navigating around spreadsheets. Free download available.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    My bad, Copy doesn't work on non-contigous areas. This should work:

    Sub SetAllSheetsToValues()
    Dim shtSheet As Worksheet, shtActive As Worksheet
    Dim rngR As Range, rngCell As Range
    Application.ScreenUpdating = False
    Set shtActive = ActiveWorkbook.ActiveSheet
    For Each shtSheet In ActiveWorkbook.Sheets
    With shtSheet
    If .ProtectContents = False Then ' skip protected sheets
    On Error Resume Next
    Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
    xlErrors + xlLogical + xlNumbers + xlTextValues)
    On Error GoTo 0
    If Not rngR Is Nothing Then
    For Each rngCell In rngR
    rngCell.Value2 = rngCell.Value2
    Next rngCell
    End If
    End If
    End With
    Next shtSheet
    shtActive.Activate ' reset to original active sheet
    [a2].Activate
    Set shtActive = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Or this shorter alternative (needs the check for protection though):

    Option Explicit

    Sub ConvertToValues(oSh As Worksheet)
    Dim oRng As Range
    Set oRng = oSh.UsedRange
    oRng.Value2 = oRng.Value2
    End Sub

    Sub ProcessAll()
    Dim oSh As Worksheet
    For Each oSh In ActiveWorkbook.Worksheets
    ConvertToValues oSh
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Shamelessly borrowing from <!profile=sdckapr>sdckapr<!/profile>, would changing the first sub to this run quicker?<pre>Sub ConvertToValues(oSh As Worksheet)
    Dim oRng As Range
    Set oRng = oSh.UsedRange<font color=red>.SpecialCells(xlCellTypeFormulas)</font color=red>
    oRng.Value2 = oRng.Value2
    End Sub</pre>

    Grüße

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    I also used that approach. The problem that I ran into and why I edited my code so <img src=/w3timages/censored.gif alt=censored border=0> much is that I forgot that the Excel Copy method does not work on discontiguous ranges; so if the SpecialCells method for formulas ends up selecting a discontigouus range, it's necessary to loop though each cell.

    I think Jan Karel's is probably the most efficient. I might have written the core part just as:

    With shtSheet.Usedrange
    .Value2 = .Value2
    End With
    etc ...
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    Mark, I know that ASAP utilities for Excel also has similar functions. But two points here, I'm looking for something specific to my needs and also smaller (read fewer choices to sort through), and I am also trying to learn the macro language, syntax, etc.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    John, Many thanks. Its fast, tight, works on the active file from "personal.xls" and from within the active file.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Term for 'Select Sheet' (Excel 97)

    unkamunka, I explained that badly. In my testing, if you use the expression

    oRng.Value2 = oRng.Value2

    the first value at the top LHS of the range gets copied to ALL cells in the Range. Hence it's necessary to loop through each cell.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •