how to reference a sheet codename in and across macros

[jinkuri]

Hi,

I'm a beginner at VBA and learning as I create. I am using the macro recorder to get ideas as well as internet searches for other people's solutions. So far, I have a macro that will make a copy of an Excel sheet (that contains some raw data) and rename the copy tab name. The new tab name also includes a date stamp. Example: Sheet1 is copied, now I have Sheet1 and Sheet2. Sheet2 is given a new tab name. I have 2 problems related to this:

Problem 1:
For the code that performs the renaming of the copy, I am using the sheet index number. When I try to use the sheet codename (Sheet2), I get an error saying the variable has not been defined. I don't understand the reason. Using the index number works but I want to avoid it in case the number of sheets keeps growing. How can I fix this to use the Sheet2 codename?

Sub copy_rename_cleanup()

    Dim addDate As String

    addDate = Format(Date, "mm-dd-yyyy")

    Dim wsSheet As Worksheet
    On Error Resume Next
    Set wsSheet = Sheets("Performance Check " & addDate)
    On Error GoTo 0

    If Not wsSheet Is Nothing Then
           MsgBox "A performance check with today's date already exists."
           Sheets("Performance Check " & addDate).Activate
    Else
           MsgBox "A performance check will be created."
           Sheet1.Copy After:=Sheet1
           Sheets(2).Activate
           Sheets(2).Name = "Performance Check " & addDate

Problem 2:
My first macro also creates a button. The button calls a 2nd macro to create a line graph using the data on Sheet2. For this, I am using:

.SetSourceData Source:=Range() to select my data

and .SeriesCollection().Name to set my y series labels

and .SeriesCollection().XValues to set my x axis values

Since the tab name of Sheet2 could have any date stamp on it, I need to refer to it by codename but I don't know how to do this. I could use some help not only with this but with suggestions on how to do this easier and/or properly. Obviously, this works when I hardcode the Sheet2 tab name but this makes the code useless once the date changes.

Sub graphPGH1()

ActiveSheet.Shapes.AddChart.Select

With ActiveChart
.ChartType = xlLine
              .SetSourceData_Source:=Range("Sheet2!$e$3:$e$39,$i$3:$i$39,$m$3:$m$39,$q$3:$q$39,$u$3:$u$39,$y$3:$y$39,$ac$3:$ac$39")
    
        .SeriesCollection(1).Name = "=Sheet2!$E$2"
    .SeriesCollection(2).Name = "=Sheet2!$i$2"
    .SeriesCollection(3).Name = "=Sheet2!$m$2"
    .SeriesCollection(4).Name = "=Sheet2!$q$2"
    .SeriesCollection(5).Name = "=Sheet2!$u$2"
    .SeriesCollection(6).Name = "=Sheet2!$y$2"
    .SeriesCollection(7).Name = "=Sheet2!$ac$2"
    .SeriesCollection(1).XValues = "=Sheet2!$A$3:$A$39"
    
        End With
    
End Sub

Thanks in advance for your help and ideas.

levi

 

[jinkuri]

I forgot to mention that for Problem #2, with the sample code I have shown, I get an error saying the Range method failed in the line that contains .SetSourceData.

Thanks.

 

[ZAX]

Are You Sure You're using The "Quotes" when typing the sheets code name in problem1 ?

 

[jinkuri]

Hi ZAX,

I might be misunderstanding your question but do you mean using double quotations around the sheet codename? When I looked for examples online, here is what I had based my code on:

using tab name: Sheets("name of my sheet").property

using index number: Sheets(#).property

using codename: Sheet#.property

Is this the right syntax?

 

[ZAX]

Yes It is,and I meant two quotes around the tab name.