Results 1 to 8 of 8

tab name in VBA

This is a discussion on tab name in VBA within the Excel Questions forums, part of the Question Forums category; is there a way for a macro to read the tab name of the sheet, and insert it in the ...

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    18

    Default

    is there a way for a macro to read the tab name of the sheet, and insert it in the coding where it references "sheets", to eliminate any manual input? thanks for any and all feedback.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    Many ways to do this, most based on the .Name property

    ActiveSheet.Name will give you the name of the active sheet, for instance.

    HTH,
    Jay

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    18

    Default

    thanks for you quick reply. of the many ways, could you pick, that which you think is the simplest, and give me a bit more detail. i would appreciate it.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi

    Code:
    Sub test()
    
    Dim ws
    
    For Each ws In ThisWorkbook.Worksheets
        MsgBox ws.Name
    Next ws
    
    For Each ws In ThisWorkbook.Sheets
        MsgBox ws.Name
    Next ws
    
    End Sub
    Here are two methods. The first will only list the worksheets, the second will also list the chart sheets, if you have any. You can write these into an array and work with the names, or test them directly.

    What exactly would you like to do? The appropriate method might depend on the task.

    Bye,
    Jay

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    18

    Default

    I have a workbook, with numerous sheets. a sheet will go to the data sheet, to get data,and i want it to return to the sheet where it started from. since different users will be working with this, i dont want to have to write a macro for each person. i would like the macro to recognize the tab from where it came, and after getting the data from the data sheet, return to the starting sheet. hope this makes sense. thanks for the help

  6. #6
    Board Regular
    Join Date
    May 2002
    Location
    Austin, TX
    Posts
    68

    Default

    If you want the macro to return to the same sheet everytime, you can try this:

    Sheets("Data").Select
    (where you have typed the tab name in the parenthesis.)

    or
    Sheets(1).Select
    (where you specify which tab to return to, based on the sheet number.)

    I'm not sure how you would make that command variable. Maybe you could start with a code that created a definition for the 'active sheet'?

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    You can do all this without selecting the data sheet. You can remain on the active sheet and transfer the data as required.

    If you do not want ot go that route, you can do something like

    Set ActWks = ActiveSheet

    Sheets("Data").Select
    rest of your code for data sheet

    ActWks.Select
    rest of your code for active sheet

    If you don't want to set an object variable to the active sheet, but do want to reference it by name

    ActWks = ActiveSheet.Name (notice I did not use 'Set' here)

    then call it like

    Sheets(ActWks).Select

    Bye,
    Jay

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    Sub mySheetData()
    '
    'By Joe Was

    'This adds a sheet and names it "Test."
    Sheets.Add.Name = "Test"

    'This selects your new sheet and moves it after sheet "Sheet3," which could be any sheet name.
    Sheets("Test").Select
    Sheets("Test").Move After:=Sheets("Sheet3")

    'this selects the sheet with the data and its range.
    Sheets("Sheet1").Select
    Range("A1:A7").Select

    'This will copy and paste the data to your new sheet "Test."
    Selection.Copy
    Sheets("Test").Select
    ActiveSheet.Paste

    'At this point your data will be on the new sheet and selected for the next step.

    End Sub

    This askes the user for a new sheet name and makes the new sheet.

    Sub IBoxSheet()
    Dim mySheet As String
    'This code asks the user for a new Sheet name.
    'Then makes a new Sheet with the users inputed name.

    mySheet = Application.InputBox(prompt:="Enter the name of your new Sheet: ", Title:="Add Sheet!", Type:=1 + 2)
    Sheets.Add.Name = mySheet
    End Sub


    Hope this helps. JSW

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