Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select home cell on worksheets (Excel 2000)

    Hi,
    I am wondering what i need to do to make this piece of code work. What i have is a large save macro which selects worksheets and transfers information to outlook and makes a datafile. inside the save code when it runs it selects different ranges for the Data sheet and leaves each worksheet on the last selected range. this is unwanted after the save macro is ran so i adding simple code at the end to select the a1 cell of each worksheet that does this.
    I cant seem to get the code beyond a testing stage. Anyone see my problem??



    Dim Vnames
    Dim x As Integer

    Vnames = Array(

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    I don't know where you wrote this code, but it contains smart quotes ("curly quotes") instead of the ordinary straight quotes VBA requires.
    If you replace all smart quotes by straight quotes, the code should work OK (provided the sheet names are valid)

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    yes i wrote the code in word, so that explains the problem
    thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    See screenshot below. To replace, just overtype the quotes in the Visual Basic Editor with <code>"</code>.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    When i declare:
    With Worksheets(Vnames(x))
    I get an error subscript out of range??


    Option Explicit
    Sub gohome()


    Dim Vnames
    Dim x As Integer

    Vnames = Array("Title Page", _
    "Procedure", "Prop Schedule", "Calc Page", "Pricing", "Input

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    In the first place, you should use

    For x = LBound(Vnames) To UBound(Vnames)

    The error you get means that there is an error in one of the sheet names you provide. Check the spelling very carefully.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    thanks Hans,

    I have the macro working but it seems to only select a1 in one page and does nothing to the others

    the code below seems to work better, but i was just trying to compress it with a loop,
    is there an easier way for me to do this because it seems the approach i took doesnot work

    thanks


    Sheets("Title Page").Select
    Range("A1").Select
    Sheets("Procedure").Select
    Range("A1").Select
    Sheets("Prop Schedule").Select
    Range("A1").Select
    Sheets("Calc Page").Select
    Range("A1").Select
    Sheets("Pricing").Select
    Range("A1").Select
    Sheets("Input").Select

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Without seeing the actual workbook it is impossible to say why the loop didn't work. The code itself is OK.

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    <P ID="nt"><font size=-1>(No Text)</font>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Thanks. I wasn't looking closely enough. You must activate a worksheet before you can select a cell in that sheet.

    With Worksheets(Vnames(x))
    .Activate
    Range("A1").Select
    End With

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Thanks hans for you Time!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Now that Hans has fixed your problem, I would like to throw in my two cents worth:

    1- I don't know what your other code looks like, but it is almost never necessary to select a range of cells to do what you want to do, and it is always better and more efficient not to select. Therefore, my first comment is that you would eliminate the need for this code if you change your other code not to select any cells.

    2- The code below should do what you want and is a little simpler and is not dependant on sheet names:

    <code>
    Dim oWS As Worksheet
    For Each oWS In Worksheets
    oWS.Activate
    oWS.Range("A1").Select
    Next oWS
    </code>
    Legare Coleman

  13. #13
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Select home cell on worksheets (Excel 2000)

    And now for my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth-

    You probably won't want to leave A1 activated if it's outside the visible/frozen range, or is hidden. The following code is my VBA equivalent to "Ctrl-Home":

    Sub CtrlHome()
    ActiveSheet.Range("A1").Activate
    ActiveWindow.VisibleRange.Cells(1).Activate
    Do While ActiveCell.EntireRow.Hidden = True
    ActiveCell.Offset(1, 0).Activate
    Loop
    Do While ActiveCell.EntireColumn.Hidden = True
    ActiveCell.Offset(0, 1).Activate
    Loop
    End Sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Your "rather long code" consists of 11 lines? (Why did you attach it as a .rtf file? A plain text file would have sufficed)

    Have you tried Legare's code? It should select A1 on all worksheets in the workbook.

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select home cell on worksheets (Excel 2000)

    Hi gentlemen,

    Thanks for your 2 cents, I used all the different techniques.

    I am having no luck though with getting my code to properly make all the sheets focus on cell a1.
    the code attached has been developed by collegue and is rather large. It basically takes an excel workbook template and
    after someone has filled it out they click a save button which runs the attached code. which then creates a data file to be attached to an outlook item. another workbook then has the ability to download off this data file.

    After the code is ran because it selects ranges from different worksheets, it leaves the last visted range on each worksheet selected and scolled to that position.

    when someone else opens the workbook saved and goto the various worksheets they need to scroll all the way to the left and all the way to the top to veiw the contents on each sheet.

    some worksheets have frozen rows at row 6 approx so i figured that if a macro is thrown in at the end of the code it would go through each worksheet, and move the sheets focus home or A10 to avoid the frozen rows, or scrolled all the way left and all the way to the top.

    right now i am scratching my head to figure why the code wont go home on every worksheet

    thanks for your replies,
    attached it the monster of code.

    thanks

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
  •