Macro stops working when I copy sheet

[excelgeekgirl]

Hi,

I put together some code to calculate the stock volatility. It requires that you enter a start date, end date, and ticker symbol, and then you push the button and excel retrieves the data from yahoo finance & runs some formulas. My problem is that I'd like to have multiple tabs for different stocks, each with it's own button, but when I create a copy of the sheet, the macro doesn't work. I'm sure it has something to do with how I'm referencing the sheet, but I don't know enough to figure it out. Also, my ideal would be that I could create a copy of a worksheet with the code in it, and the macro button on that sheet would update automatically for the new sheet reference.

Thank you in advance for all the help!

Sub GetData()
    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    Set DataSheet = ActiveSheet
  
        StartDate = DataSheet.Range("B1").Value
        EndDate = DataSheet.Range("B2").Value
        Symbol = DataSheet.Range("B3").Value
        Range("C7").CurrentRegion.ClearContents
        
        qurl = "[URL]http://ichart.yahoo.com/table.csv?s[/URL]=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"
        
                   
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
            
            Range(Range("C8"), Range("C8").End(xlDown)).NumberFormat = "mm/dd/yy"
            Range(Range("D8"), Range("G8").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H8"), Range("H8").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I8"), Range("I8").End(xlDown)).NumberFormat = "0.00"
            Range(Range("J8"), Range("J8").End(xlDown)).NumberFormat = "0.0000"
            Range(Range("K8"), Range("K8").End(xlDown)).NumberFormat = "0.0000"
 
  
  Dim Column_J_J As Long
    Column_J_J = Range("I" & Rows.Count).End(xlUp).Row - 1
    Range("J8").Formula = "=RC[-1]/R[1]C[-1]-1"
    Range("J8").AutoFill Destination:=Range("J8:J" & Column_J_J), Type:=xlFillSeries
  
    Column_K_K = Range("I" & Rows.Count).End(xlUp).Row - 1
    Range("K8").Formula = "=IF(R[1]C[-2]<0.0000001,no data,LN(RC[-2]/R[1]C[-2]))"
    Range("K8").AutoFill Destination:=Range("K8:K" & Column_K_K), Type:=xlFillSeries
      

    Range("J7").Select
    ActiveCell.FormulaR1C1 = "Return"
    Range("J7").Select
    Range("K7").Select
    ActiveCell.FormulaR1C1 = "Natural Log"
    Range("K7").Select
Application.Calculation = xlCalculationAutomatic
Columns("C:K").EntireColumn.AutoFit

End Sub

 

[Jerry Sullivan]

Hi,

This worked for me when I copied it to another sheet provided I used an ActiveX button.
It failed when using a Form button in the worksheet.

Check to see which you are using and change it to an ActiveX button if needed.

The sheet reference shouldn't be the problem since the only sheet references are to ActiveSheet.

 

[excelgeekgirl]

Thanks for the response. I've never created an ActiveX button, but I'm thankful for the introduction.

Thank you!

 

[Jerry Sullivan]

Thanks for the response. I've never created an ActiveX button, but I'm thankful for the introduction.

Thank you!

You insert it into your worksheet from the Developer > Toolbox the same way as you did for your Form button.

When you insert a Form button, it pops up and asks what macro you want to associate. With the ActiveX button, you click on the button when you are in Design mode and it sends you to the CommandButton1_Click code.