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?
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.
Thanks in advance for your help and ideas.
levi
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?
Code:
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.
Code:
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