Get the name of worksheet behind active worksheet

[Les Stout]

Hi, i am trying to piece together some code to do the above. When i open
the file the active sheet name will always be "New gAMS" and the sheet
behind it will be a date "xx_xx_xx" and i need this sheet name as a
string variable to use further on..

Any help would be appreciated..

Les Stout

 

[Guest]

Les,

Are you saying that you have two sheets in your workbook and that the first
worksheet is "New gAMS". You need the name of the second sheet in the
book? Is this correct?

 

[Guest]

Can you make any modifications in this workbook now? If so. you might want
to give the date sheet a code name that you can control. Let me know and we
can go further.

 

[Les Stout]

Hi Barb, it is a report that i generate weekly and name the sheet with
the day's date and then the difference of the last sheet to the new
sheet is put into the sheet New gAMS..

Les Stout

 

[Guest]

Alt F11 to see the VB editor
Ctrl R to see the Project Explorer.
Select the sheet that is for the date.
F4 to see the properties for that sheet.

The name of the sheet should be displayed. It probably shows Sheet1, Sheet2
or something like that. Change it to something that makes sense for the
sheet. Let's say you call it DATE

to get the name that's on the tab for the sheet, you'll need DATE.Name

Try that and come back with more questions.

 

[Guest]

Just discovered that DATE is not a legal name. Try something else.

 

[Les]

Hi Barb, i tried that, called it "LastSheet" but cant get it to work...
sorry not a programmer but a dabbler..

 

[Les]

Hi Barb, i tried that, called it "LastSheet" but cant get it to work...
sorry not a programmer but a dabbler..

 

[Guest]

what do you have for code so far?

Hi Barb, i tried that, called it "LastSheet" but cant get it to work...
sorry not a programmer but a dabbler..

 

[Les Stout]

Hi Barb, herewith code so far.

Sub TestNames()
Dim sh As Worksheet
Dim shName As String
Workbooks("gAMS_Report_Sep_06.xls").Worksheets(LastSheet).Name = sh
shName = sh
MsgBox shName

End Sub


Les Stout

 

[Guest]

Let's start this again. When you open the workbook, you want the new Gams
worksheet to be at the top. Change the code name for the "New gAMS" sheet
to New_GAMs.


On the project explorer page, you'll also see ThisWorkbook. Right click on
that and select View Code

Click on GENERAL and pull down to Workbook. Mine defaulted to Workbook_Open

Put this code in there

Private Sub Workbook_Open()
Sheets(New_GAMs.Name).Select
End Sub

You'll always get the New gAMs sheet selected when you open the workbook.

 

[Les]

Hi barb, the worksheet "New gAMS" is always on top when the workbook
opens, i need to know the tab name of the sheet behind it. However i
think that i have managed to get it with:
"Application.Workbooks("gAMS_Report_Sep_06.xls").Sheets(2).Name". as i
always put the new sheet in front so the last sheet would always be
sheet1 = "New gAMS", sheet2 = the sheet behind.....

Thanks so much for the input

Best regards,

Les

 

[Ken McLennan]

G'day there Les,

Hi barb, the worksheet "New gAMS" is always on top when the workbook
opens, i need to know the tab name of the sheet behind it. However i
think that i have managed to get it with:
"Application.Workbooks("gAMS_Report_Sep_06.xls").Sheets(2).Name". as i
always put the new sheet in front so the last sheet would always be
sheet1 = "New gAMS", sheet2 = the sheet behind.....

I came into this thread a little late, so you may well have
everything in hand, but I was able to get the sheet name this way:

Public Sub test()

Dim iX As Integer
Dim iY As Integer
Dim sNAME As String

iX = ThisWorkbook.ActiveSheet.Index
iY = iX + 1

sNAME = Sheets(iY).Name

End Sub

Perhaps you could derive something from that?