tab name in VBA

[bigfoot2]

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.

 

[Jay Petrulis]

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

 

[bigfoot2]

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.

 

[Jay Petrulis]

Hi

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

 

[bigfoot2]

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

 

[agiammo]

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'?

 

[Jay Petrulis]

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

 

[Joe Was]

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