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

*** Sent via Developersdex http://www.developersdex.com ***

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?


"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

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.

"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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


Barb Reinhardt wrote:
Just discovered that DATE is not a legal name. Try something else.

"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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


Barb Reinhardt wrote:
Just discovered that DATE is not a legal name. Try something else.

"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

what do you have for code so far?

"Les" wrote:

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


Barb Reinhardt wrote:
Just discovered that DATE is not a legal name. Try something else.

"Les Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

Hi Barb, herewith code so far.

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

End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

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 Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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").S heets(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

Barb Reinhardt wrote:
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 Stout" wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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").S heets(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?

--
See ya,
Ken McLennan
Qld, Australia