Name a Sheet a variable?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Name a Sheet a variable?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is it possible to make my macros a BIT shorter by defining "Sheets("sheetname")" as a variable in my macro? I know i can use:
    variable=activeworksheet.name

    then :

    worksheets(variable).select

    but i cant do it like this?:

    Sub test()
    Dim DataSheet As Worksheet
    Dim CurrFile

    CurrFile = ActiveWorkbook.Name
    DataSheet = Worksheets("DataSheet")

    BackUpRange = DataSheet.Range("a1:a5")
    end sub

    Just wondering, thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why do you want to do this?

    You are trying to set an object variable to a string variable. I don't think you can do that.

    Wait, is this what you want to do?
    Code:
    Dim DataSheet As Worksheet
    Dim BackUpRange As Range
    Set DataSheet = Worksheets("Sheet1")
    Set BackUpRange = DataSheet.Range("a1:a5")
    [ This Message was edited by: Al Chara on 2002-04-17 15:12 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    file names seem to be comfusing in VBA suggest add time to end of whatever file name them covers any reason poss date also,, whats more varialbe kinda off,

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al, thats exactly what i was looking for, thanks much

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    If you go into VBA and look at the project explorer it will show you a list of all the worksheets. For example:
    Sheet1 (DataSheet)
    Sheet2 (Sheet2)
    When you change the sheet tab name you just change the name inside ()
    You can change the name outside the () by clicking on it and then hitting F4 (to bring up the properties window).
    Change (Name) to DataSheet (for example) and now you can do the following:
    DataSheet.Range("a1:a5")
    instead of Worksheets("DataRange").Range("a1:a5")
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com