Worksheet Name To Variable

jamesparker_1 · Apr 4th 2007

Hi Sorry, I feel like this is an obvious one and I should know it but why wont this work?

Code

Sitenameshort = "_Bmth"


    ShtName1 = "Weekly" & Sitenameshort
   
    shtName = ShtName1.Name 'This line doesnt work
    
    Set Nws = Sheets(shtName)

It Errors on the marked line.

Thanks

James

**norie** · Apr 4th 2007

Re: Worksheet Name To Variable

James

I don't see why it would work.:)

What are you actually trying to do?

Are you trying to use the sheet's codename?

Todkerr · Apr 4th 2007

Re: Worksheet Name To Variable

If your goal is to set nws to be sheets("weekly_Bmth")...

You posted:

Code

shtName = ShtName1.Name 'This line doesnt work

I believe the ".name" is the offending item.

try:

Code

shtName = ShtName1

ShtName1 is already returning a text variable...

Of course this all seems redundant anyway since you could simply:

Code

nws = sheets(ShtName)

perhaps I am missing what you are trying to do?

jamesparker_1 · Apr 4th 2007

Re: Worksheet Name To Variable

Hi guys

Sorry, I probably didnt make myself clear.

ShtName1 is the codename (i.e. Weekly_Bmth)

therefore:

shtName = ShtName1.Name is because I want shtName to be whatever the tab name (i.e. the .name) is of the worksheet with the codename above.

James

Todkerr · Apr 4th 2007

Re: Worksheet Name To Variable

OK - I think I understand you....but as written, ShtName1 is retuning a concatenated text string - not a sheet object.

DO you need:

Code

shtName = sheets(ShtName1).Name

To return the sheet object?

jamesparker_1 · Apr 4th 2007

Re: Worksheet Name To Variable

That doesn't seem to work i'm afraid.

Here is the total code as maybe that will make things clearer:

Code

Public Sub CopyOver()
   
   With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
     
    Dim WwB As Workbook
    Dim Ws, Nws, sSht As Worksheet
    Dim Shtname As String
    Dim dteDate As Date
    
    'Select Site
    Sitenamefull = " " & "Bournemouth"
    Sitenameshort = "_Bmth"
        
    
    'Open Site File**************************************
    SavePath = Weekly_Main.Range("J1").Value
    SiteFile = SavePath & "\Weekly Metric Template for ppt_EMEA  "
    
    dteDate = Weekly_Main.Range("I1")
    Day_Str$ = Format$(dteDate, "dd_mm_yyyy")
    'Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & " Bournemouth" & ".xls")
     
    
    'Weekly Sheet Update*************************************************
    shtName1 = "Weekly" & Sitenameshort
    'shtName = ShtName1.Name         'Error!!!!!!!!!!!!!!!!!!!!!!!!!11
        
    Set Nws = Sheets(Shtname)
     
    Set WwB = Workbooks.Open(Filename:=SiteFile & Day_Str$ & Sitenamefull & ".xls")
     
    Set Ws = WwB.Worksheets(4)
     
    With Ws.Cells.Copy
    End With
     
    Nws.Cells.PasteSpecial xlPasteAll
     
    Set Nws = Nothing
    Set Ws = Nothing
    'Rec Sheet Update*************************************************
    
    
    
    
    'Close Site sheet *******************************************
    WwB.Close False
    Set WwB = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
     
End Sub

Display More

**Kenneth Hobson** · Apr 4th 2007

Re: Worksheet Name To Variable

Try setting it by checking the CodeName for the worksheets collection.

Post back with your efforts either way.

**norie** · Apr 4th 2007

Re: Worksheet Name To Variable

Do you really need to use the codename?

jamesparker_1 · Apr 5th 2007

Re: Worksheet Name To Variable

Hi Guys,

Unfortunately, due to the nature of the program. It is likely that people will try and rename the sheet tabs. So therefore I try to work with codenames to avoid errors when they change something. So I think i need to do it this way.

Any suggestions?

Thanks

James

shg · Apr 5th 2007

Re: Worksheet Name To Variable

I think it cannot bind a string to a variable by that name at run-time.

jamesparker_1 · Apr 5th 2007

Re: Worksheet Name To Variable

Hi Guys!

That's a real shame I cant do that. I can work around it but It would have tidyed things up in my code.

Thanks for trying though!

James

**Kenneth Hobson** · Apr 5th 2007

Re: Worksheet Name To Variable

Does this approach not work for you?

Code

Sub ShowSheetName()
Dim Sitenameshort As String
Dim ShtName1 As Worksheet
Dim shtName As String
Dim Nws As Worksheet
Dim ws As Worksheet


Sitenameshort = "_Bmth"


For Each ws In Worksheets
  If ws.CodeName = "Weekly" & Sitenameshort Then
    Set ShtName1 = ws
  End If
Next ws


If ShtName1 Is Nothing Then
  MsgBox "Sheet CodeName " & "Weekly" & Sitenameshort & " was not found.", , "Error"
  Exit Sub
End If
  
shtName = ShtName1.Name 'This line doesnt work


Set Nws = Sheets(shtName)


MsgBox shtName, , Nws.Name


End Sub

Display More

jamesparker_1 · Apr 5th 2007

Re: Worksheet Name To Variable

Hi Kenneth

Great stuff! Thanks! That really works great!

Thanks so much for your help!

James

Worksheet Name To Variable

Participate now!

Share