+ Reply to Thread
Results 1 to 7 of 7

Exisiting code to work regardless of worksheet name

  1. #1
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141

    Exisiting code to work regardless of worksheet name

    This is my current code. Currently the worksheet is named "blank". I need this code to execute even if I change the name. Currently it throws me an error.
    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: Exisiting code to work regardless of worksheet name

    Your Worksheets("blank") is part of the worksheets collection.

    Try using indices like worksheets(1) to refer to the first
    Or Worksheets(Worksheets.count) to refer to the last worksheet

    Gr,
    Ricardo

  3. #3
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    Your last suggestion works fine so far. Thank You.

  4. #4
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    Well I jumped the gun it seems. Once i close and reopen it only updates the first sheet and not the one recently copied. I am thinking there is an activesheet reference that I could use perhaps...

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202
    Are you running the code whilst "blank" is active? If so the use
    Please Login or Register  to view this content.
    If not you need to get the sheets Code Name. If you look at the Properties Window of the VB Editor you will see the workbooks & their sheets listed., eg Sheet1(Blank). Sheet1 is what you want.
    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    Is this what you mean?
    Please Login or Register  to view this content.
    When I do this i get a error on that line. Perhaps its something I am not understanding.

  7. #7
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Question try

    Why don't you name a range in that worksheet i.e. "SheetName"

    then fut this formula in it:

    Please Login or Register  to view this content.
    Then use:

    Please Login or Register  to view this content.
    THis way whenever you change the sheet name the cell will change and you will always be refering to the correct sheet!! ?
    Mr MaGoo
    Magoo.Inc MMVII

    If i've helped please add to my Rep by Clicking on the Blue Scales in the top right hand corner of the post

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VB code error when workbook loses focus
    By Protiusmime in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-12-2008, 12:43 AM
  2. Replies: 1
    Last Post: 04-06-2008, 11:20 PM
  3. Worksheet count and reference issue
    By danny2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2007, 10:45 PM
  4. Why does this code not work in excel 2007 ?
    By spyrule in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2007, 02:41 AM
  5. Code to List all worksheet names (tab names)
    By howard101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2007, 12:51 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1