0

I am making a code that plots each row (2-18) in a different worksheet. I want to delete and add new worksheets every time the button is pressed to avoid duplicate plots. The code works with the delete and add lines commented out, but when I uncomment I receive a runtime error 

For RowIndex = 2 To 18 'Repeats for each line
   sht = Cells(RowIndex, 2).Value 'Worksheet name is in column B
'   Worksheets(sht).Delete 'Deletes current worksheet for every tag
'   Worksheets.Add.Name = sht 'Creates blank new worksheet for every tag
'   Worksheets(sht).Move _
'   After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)   'Moves new sheet to end 
   CreateChart RowIndex, sht, DateColInd
Next RowIndex

'CODE GIVES ERROR AT SET XVALUES LINE

Sub CreateChart(Row, sht, DateColInd) 'DateColInd is the column index for today's date 
   Dim xValues As Range
   Dim yValues As Range
   Dim Target As Range
   Dim Limit As Range
   Dim cht As Chart
   Set xValues = Worksheets("Data Archive").Range(Cells(1, 3), Cells(1, 
   DateColInd)) 'Gets all data per given line

I want it to reset all worksheets for each line, each with a plot. When the lines are commented out, it creates the plot fine. But when the lines are not commented out I receive a runtime error and it fails

BigBen
  • 46,229
  • 7
  • 24
  • 40
DaveKneiss
  • 1
  • Possible duplicate of [Run time error 1004 in Range(Cells())](https://stackoverflow.com/questions/32613372/run-time-error-1004-in-rangecells). Your `Cells` references are unqualified, meaning the `Worksheet` they are on is not specified. There is an implicit reference to the `ActiveSheet`, which may or may not be the Data Archive sheet. – BigBen May 03 '19 at 14:42
  • That's it--fixed it with a Worksheets("Data Archive").Activate before calling CreateChart. Thanks! – DaveKneiss May 03 '19 at 14:55
  • `Activate` and relying on the `ActiveSheet` is generally frowned on. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more detail. Or the linked question in my first comment for a robust way to fix the issue. – BigBen May 03 '19 at 15:02

0 Answers0