CODEWITHSUNDEEP
excelvba
svkks
svkks

Reputation: 95

Excel sheets with VB code running gives error while opening new workbook

I have an excel sheet with button start and stop. If I click button start, it starts getting values from sheet named Prices until I click stop button. It works perfectly. But when I open a new empty excel sheet it gives an error "subscript out of range". My problem is first excel should work at background if I press start button and I should be able to open any number of other workbooks.

I have given below part of my code where error comes.

Sub StartDataUpdate()
    TimerActive = True
    UpdateData
End Sub

Private Sub UpdateData()
    If TimerActive Then

        ConnectDB

        Set rs1 = New ADODB.Recordset
        Dim LocalTime As String
        LocalTime = Format(Now(), "YYYY/MM/DD HH:MM:SS")

        With Sheets("Price")
            For rowCursor = 2 To 10
                 //Code to insert data in database
            Next

        End With

        Set rs1.ActiveConnection = Nothing
        oConn.Close
        RepeatUpdate
    End If
End Sub

Error comes at the line With Sheets("Price") as subscript out of range when I open a new empty excel sheet.

Upvotes: 1

Views: 108

Answers (1)

Alex de Jong
Alex de Jong

Reputation: 1267

Sheets("Price") is not always set to the workbook of the macro. Try ThisWorkbook.Sheets("Price") In that case, it will only look at the workbook where the macro is in.

Upvotes: 2

Related Questions